﻿/*
**** This script file is never executed ****

It contains the schema of the database before DBScripts was implemented (technically after 'Script 0011').

This file shouldn't be updated, it is merely for reference. So that if a SQL-Server schema modification needs to be rolled
back during development, we have a copy of what the SQL objects used to be.
*/

USE [nexdox_BBOS_DEV]
GO
/****** Object:  StoredProcedure [dbo].[AddGroupsToUser]    Script Date: 21/04/2015 11:34:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[AddGroupsToUser] 
	@UserId nvarchar(128),
	@GroupIDs varchar(100)
AS
BEGIN
	
	SET NOCOUNT ON;
	
	declare @GroupIDsTable table(GroupId int)
	
	Insert into @GroupIDsTable
    select SplitValue from [dbo].[Split](@GroupIDs,',')	
    
    Delete From [dbo].[UserGroups]
    where UserId=@UserId
    
    While((select COUNT(*) from @GroupIDsTable )>0)
	Begin
		  
	  declare @groupId int;
	  select top 1 @groupId= GroupId from @GroupIDsTable
	  
	  Insert Into [dbo].[UserGroups] (UserId,GroupID)
	  values(@UserId,@groupId)
	  
	  delete from @GroupIDsTable where GroupId=@groupId
	  
	End
	
END

GO
/****** Object:  StoredProcedure [dbo].[AddMediaStock]    Script Date: 21/04/2015 11:34:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[AddMediaStock]
	@StockId INT,
	@MediaDefId INT,
	@StationNumber INT
AS

INSERT INTO MediaStock
(StockID, MediaDefID, PublishDate, StationNumber)
VALUES
(@StockId, @MediaDefId, GETDATE(), @StationNumber)

SELECT Stock.StockId, Code, [Description], [Weight], SizeX, SizeY, Facing, Colour, Stock.StockTypeId, StockType, MediaStockId, MediaDefId, PublishDate, StationNumber
FROM dbo.MediaStock
INNER JOIN dbo.Stock ON MediaStock.StockID = Stock.StockID
INNER JOIN dbo.StockType ON StockType.StockTypeID = Stock.StockTypeID
WHERE MediaStockID = SCOPE_IDENTITY()
GO
/****** Object:  StoredProcedure [dbo].[CreateApprovalHistory]    Script Date: 21/04/2015 11:34:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[CreateApprovalHistory]

@JobId int,
@UserId nvarchar(128),
@Comment nvarchar(500),
@ApprovalDate Date

as

Begin

insert into ApprovalHistory (JobID, UserID, Comment, ApprovalDate) values
(@JobId, @UserId, @Comment, @ApprovalDate)

End
GO
/****** Object:  StoredProcedure [dbo].[CreateAuthoriseHistory]    Script Date: 21/04/2015 11:34:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[CreateAuthoriseHistory]

@JobId int,
@UserId nvarchar(128),
@Comment nvarchar(500)

as

Begin

insert into AuthoriseHistory (JobID, UserID, Comment, ApprovalDate) values
(@JobId, @UserId, @Comment, GETDATE())

End
GO
/****** Object:  StoredProcedure [dbo].[CreateInputFile]    Script Date: 21/04/2015 11:34:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[CreateInputFile] 
	-- Add the parameters for the stored procedure here
	@Filename nvarchar(250),
    @FilePath nvarchar(250),    
    @JobID int,
    @ManCoDocID int
    
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	INSERT INTO [InputFile]
           ([Filename]
           ,[FilePath]
           ,[ReceivedDate]
           ,[JobID]
           ,[ManCoDocID]
           ,[FileTypeID])
     VALUES
           (@Filename
           ,@FilePath
           ,GETDATE()
           ,@JobID
           ,@ManCoDocID
           ,1)
END

GO
/****** Object:  StoredProcedure [dbo].[CreateJob]    Script Date: 21/04/2015 11:34:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[CreateJob]
	-- Add the parameters for the stored procedure here
	@JobID int output,
	@ManCoDocID int,
    @GRID nvarchar(30),
    --@createDate datetime = GetDate,    
    --@SubmissionDate datetime = GetDate,
    @AdditionalSetupInfo nvarchar(500),
    @UserID nvarchar(128),
	@JobStatusTypeId int,
	@Comment nvarchar(500)=NULL    
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	INSERT INTO [Job]
           ([ManCoDocID]
           ,[GRID]
           ,[createDate]         
           ,[SubmissionDate]
           ,[AdditionalSetupInfo]
           ,[UserID]
		   ,[JobStatusTypeID],
		   ManuallySubmitted,
		   [Comment])
     VALUES
			(@ManCoDocID
			,@GRID
            ,GetDate()         
            ,GetDate()
            ,@AdditionalSetupInfo
			,@UserID
			,@JobStatusTypeId,
			1,
			@Comment)
			
			Set @JobID =  @@Identity
		

END


GO
/****** Object:  StoredProcedure [dbo].[CreateStock]    Script Date: 21/04/2015 11:34:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[CreateStock] 
	@StockTypeId INT,
	@Code VARCHAR(50),
	@Description VARCHAR(255),
	@Weight INT,
	@SizeX INT,
	@SizeY INT,
	@Facing CHAR(1),
	@Colour VARCHAR(50)
AS

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

DECLARE @NextStockId INT
SELECT @NextStockId = MAX(StockId) + 1 FROM [dbo].[Stock]

INSERT INTO
[dbo].[Stock] (StockId, Code, [Description], [Weight], SizeX, SizeY, Facing, StockTypeID, DateCreated, Colour)
VALUES (@NextStockId, @Code, @Description, @Weight, @SizeX, @SizeY, @Facing, @StockTypeId, GETDATE(), @Colour)

SELECT Stock.StockId, Code, [Description], [Weight], SizeX, SizeY, Facing, Colour, Stock.StockTypeId, StockType
FROM dbo.Stock
INNER JOIN dbo.StockType ON StockType.StockTypeID = Stock.StockTypeID
WHERE StockID = @NextStockId
GO
/****** Object:  StoredProcedure [dbo].[DeleteMediaStock]    Script Date: 21/04/2015 11:34:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[DeleteMediaStock] 
	@MediaStockId INT
AS

DELETE FROM dbo.MediaStock WHERE MediaStockID = @MediaStockId
GO
/****** Object:  StoredProcedure [dbo].[GetAppEnvironments]    Script Date: 21/04/2015 11:34:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[GetAppEnvironments] 
	-- Add the parameters for the stored procedure here
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT * FROM AppEnvironment 
END

GO
/****** Object:  StoredProcedure [dbo].[GetAppEnvironmentsByUserId]    Script Date: 21/04/2015 11:34:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
Create PROCEDURE [dbo].[GetAppEnvironmentsByUserId] 
	-- Add the parameters for the stored procedure here
	@UserId nvarchar(128)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT DISTINCT AppEnvironment.AppEnvironmentId, AppEnvironment.Name
	FROM UserGroups 
		INNER JOIN AspNetUsers ON UserGroups.UserId = AspNetUsers.Id 
		INNER JOIN [Group] ON UserGroups.GroupID = [Group].GroupID 
		INNER JOIN GroupDataRights ON [Group].GroupID = GroupDataRights.GroupID 
		INNER JOIN AppEnvironment ON GroupDataRights.EnvironmentID = AppEnvironment.AppEnvironmentID
    WHERE AspNetUsers.Id = @UserId
END

GO
/****** Object:  StoredProcedure [dbo].[GetCompletedJobsByManCo]    Script Date: 21/04/2015 11:34:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[GetCompletedJobsByManCo]
	-- Add the parameters for the stored procedure here
	@ManCo nvarchar(128),
	@UserId nvarchar(128)
	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT DISTINCT SUBSTRING(EnvironmentType.EnvironmentType, 1, 1) + ManagementCompany.ManCoCode + DocumentType.BravuraDocTypeCode + rtrim(REPLICATE('0',8 - LEN(Job.JobID)) + cast(Job.JobID as varchar)) JobReference, 
					job.* 
	FROM Job
		INNER JOIN JobStatusType on Job.JobStatusTypeID = JobStatusType.JobStatusTypeID
		INNER JOIN ManCoDocs on ManCoDocs.ManCoDocID = job.ManCoDocID
		INNER JOIN GroupDataRights on GroupDataRights.ManCoDocID = ManCoDocs.ManCoDocID
		INNER JOIN [Group] on [Group].GroupID = GroupDataRights.GroupID
		INNER JOIN UserGroups on UserGroups.GroupID = [Group].GroupID
		INNER JOIN AspNetUsers on AspNetUsers.Id = UserGroups.UserId
		INNER JOIN ManagementCompany on ManagementCompany.ManCoID = ManCoDocs.ManCoID
		INNER JOIN EnvironmentType on EnvironmentType.EnvironmentID = ManCoDocs.EnvironmentID
		INNER JOIN DocumentType on ManCoDocs.DocumentTypeID = DocumentType.DocumentTypeID
	WHERE JobStatusType.JobStatusDescription = 'Complete'
		AND AspNetUsers.Id = @UserId
		AND ManagementCompany.ManCoCode = @ManCo
		
	
END


GO
/****** Object:  StoredProcedure [dbo].[GetDocType]    Script Date: 21/04/2015 11:34:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[GetDocType] 
	-- Add the parameters for the stored procedure here	
	@Code nvarchar(128),	
	@Name nvarchar(128)	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT DISTINCT DocumentType.*
	FROM DocumentType 
    WHERE 		
		DocumentType.BravuraDocTypeCode = @Code AND
		DocumentType.DocumentTypeName = @Name
	ORDER BY DocumentType.DocumentTypeName
END
GO
/****** Object:  StoredProcedure [dbo].[GetDocTypes]    Script Date: 21/04/2015 11:34:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[GetDocTypes] 
	-- Add the parameters for the stored procedure here
	@UserId nvarchar(128),
	@ManCo nvarchar(128)	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT DISTINCT DocumentType.*
	FROM UserGroups 
		INNER JOIN AspNetUsers ON UserGroups.UserId = AspNetUsers.Id 
		INNER JOIN [Group] ON UserGroups.GroupID = [Group].GroupID 
		INNER JOIN GroupDataRights ON [Group].GroupID = GroupDataRights.GroupID 
		INNER JOIN ManCoDocs ON GroupDataRights.ManCoDocID = ManCoDocs.ManCoDocID
		INNER JOIN ManagementCompany ON ManCoDocs.ManCoID = ManagementCompany.ManCoID
		INNER JOIN DocumentType ON ManCoDocs.DocumentTypeID = DocumentType.DocumentTypeID
    WHERE 
		AspNetUsers.Id = @UserId AND		
		ManagementCompany.ManCoCode = @ManCo
	ORDER BY DocumentType.DocumentTypeName
END

GO
/****** Object:  StoredProcedure [dbo].[GetEnclosingJobsByJobId]    Script Date: 21/04/2015 11:34:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE Procedure [dbo].[GetEnclosingJobsByJobId]

@JobId int

as
Begin

	Select  EnclosingJobID,FBE_ID,JobID,MediaDefID,Packs
			,Pages,Sheets,[Filename],DigiQGRID,PostalDocketNumber 
	from [dbo].[EnclosingJob]
	where JobID=@JobId
	
end
GO
/****** Object:  StoredProcedure [dbo].[GetEnvironmentServers]    Script Date: 21/04/2015 11:34:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE Procedure [dbo].[GetEnvironmentServers]

@EnvironmentType varchar(50)

as

begin

select b.ProcessingServerName ServerName, b.EnvironmentType, b.EnvironmentID EnvironmentTypeId
from AppEnvironment a inner join EnvironmentType b
on a.AppEnvironmentID = b.AppEnvironmentID
where a.Name = @EnvironmentType

end

GO
/****** Object:  StoredProcedure [dbo].[GetGetManualSubmission]    Script Date: 21/04/2015 11:34:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[GetGetManualSubmission] 
	-- Add the parameters for the stored procedure here
	@ManCo varchar(2),
	@DocTypeID varchar(3),
	@EnvironmentID varchar(1)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	SELECT  
		isnull(ManualSubmissionOnly, 0)
	FROM 
		ManCoDocs md
	INNER JOIN
		ManagementCompany mc on	mc.ManCoID = md.ManCoID
	INNER JOIN 
		DocumentType dt on dt.DocumentTypeID = md.DocumentTypeID
	WHERE
		mc.ManCoCode = @ManCo and 
		dt.BravuraDocTypeCode = @DocTypeID and 
		md.EnvironmentID = @EnvironmentID
END

GO
/****** Object:  StoredProcedure [dbo].[GetGroups]    Script Date: 21/04/2015 11:34:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[GetGroups] 

AS
BEGIN

	SET NOCOUNT ON;

	SELECT * 
	FROM [Group] 
END

GO
/****** Object:  StoredProcedure [dbo].[GetGroupsByUserId]    Script Date: 21/04/2015 11:34:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[GetGroupsByUserId] 
	@UserId nvarchar(128)
AS
BEGIN
	
	SET NOCOUNT ON;

	SELECT g.GroupID, g.GroupName ,ug.UserId,ug.UserGrp_ID
	FROM [Group] g
	INNER JOIN UserGroups ug on g.GroupID = ug.GroupID
	WHERE ug.UserId = @UserId
END

GO
/****** Object:  StoredProcedure [dbo].[GetInputFile]    Script Date: 21/04/2015 11:34:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[GetInputFile]
	-- Add the parameters for the stored procedure here
	@Filename nvarchar(250),
	@FilePath nvarchar(250)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT [FilenameID]
      ,[Filename]
      ,[FilePath]
      ,[ReceivedDate]
      ,[JobID]
      ,[ManCoDocID]
      ,[FileTypeID]
  FROM [dbo].[InputFile]
  WHERE
	[Filename] = @Filename AND
	[FilePath] = @FilePath


END

GO
/****** Object:  StoredProcedure [dbo].[GetInputFileListByJobId]    Script Date: 21/04/2015 11:34:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [dbo].[GetInputFileListByJobId]

@JobId int

as

begin

select b.FilePath + '\' + b.[Filename] as FilePath
from Job a inner join InputFile b on a.JobID = b.JobID
where b.JobID = @JobId

end
GO
/****** Object:  StoredProcedure [dbo].[GetJobById]    Script Date: 21/04/2015 11:34:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create Procedure [dbo].[GetJobById]

@UserId varchar(100),
@JobId int

as

Begin

Declare @tablevar table(manCoId int)

insert into @tablevar Exec GetManCoIds @UserId

select distinct i.BravuraDOCSEnvironmentChar + c.ManCoCode + d.BravuraDocTypeCode + rtrim(REPLICATE('0',8 - LEN(a.JobID)) + cast(a.JobID as varchar)) JobReference,
 c.ManCoShortName Company, a.GRID, d.DocumentTypeName Document, b.[Version], CONVERT(CHAR(10), a.SubmissionDate, 103) +' '+ CONVERT(CHAR(9), a.SubmissionDate, 108) as SubmitDateTime,
  f.JobStatusDescription [Status], h.FirstName +' '+ h.LastName as [Owner],
b.EnvironmentID EnvironmentId, b.ManCoDocID, b.DocumentTypeID, a.JobID JobId, g.Hold HoldAuthorisation, d.BravuraDocTypeCode DocTypeCode, 
c.ManCoCode,i.BravuraDOCSEnvironmentChar EnvType, b.Authorise ,b.ManualSubmissionOnly, j.JobAuthorise, k.UserName Authoriser,
a.AllocatorGRID
from Job a inner join ManCoDocs b on a.ManCoDocID = b.ManCoDocID
inner join ManagementCompany c on b.ManCoID = c.ManCoID
inner join DocumentType d on b.DocumentTypeID = d.DocumentTypeID
inner join JobStatusType f on f.JobStatusTypeID = a.JobStatusTypeID
left join MediaDefinition g on g.ManCoDocID = b.ManCoDocID
inner join AspNetUsers h on h.Id = a.UserID
inner join EnvironmentType i on i.EnvironmentID = b.EnvironmentID
inner join JobAuthoriseView j on a.ManCoDocID = j.ManCoDocId
left join UserAuthorisedView k on a.JobID = k.JobID
inner join UserDocTypeView l on b.DocumentTypeID = l.DocumentTypeID
where b.ManCoID in (select manCoId from @tablevar)
and l.UserId = @UserId and a.JobID=@JobId
order by a.JobID desc

end
GO
/****** Object:  StoredProcedure [dbo].[GetJobs]    Script Date: 21/04/2015 11:34:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE Procedure [dbo].[GetJobs]

@UserId varchar(100),
@AppEnvironment varchar(128)

as

Begin

Declare @tablevar table(manCoDocId int)

insert into @tablevar Exec GetManCoDocIds @UserId

select distinct i.BravuraDOCSEnvironmentChar + c.ManCoCode + d.BravuraDocTypeCode + rtrim(REPLICATE('0',8 - LEN(a.JobID)) + cast(a.JobID as varchar)) JobReference,
 c.ManCoShortName Company, a.GRID, d.DocumentTypeName Document, b.[Version], CONVERT(CHAR(10), a.SubmissionDate, 103) +' '+ CONVERT(CHAR(9), a.SubmissionDate, 108) as SubmitDateTime,
  f.JobStatusDescription [Status], h.FirstName +' '+ h.LastName as [Owner],
b.EnvironmentID EnvironmentId, b.ManCoDocID, b.DocumentTypeID, a.JobID JobId, d.BravuraDocTypeCode DocTypeCode, 
c.ManCoCode,i.BravuraDOCSEnvironmentChar EnvType, b.Authorise ,b.ManualSubmissionOnly, j.JobAuthorise, k.FirstName+' ' +k.LastName Authoriser,
a.AllocatorGRID
from Job a inner join ManCoDocs b on a.ManCoDocID = b.ManCoDocID
inner join ManagementCompany c on b.ManCoID = c.ManCoID
inner join DocumentType d on b.DocumentTypeID = d.DocumentTypeID
inner join JobStatusType f on f.JobStatusTypeID = a.JobStatusTypeID
left join MediaDefinition g on g.ManCoDocID = b.ManCoDocID
inner join AspNetUsers h on h.Id = a.UserID
inner join EnvironmentType i on i.EnvironmentID = b.EnvironmentID
inner join AppEnvironment ae on ae.AppEnvironmentID=i.AppEnvironmentID
inner join JobAuthoriseView j on a.ManCoDocID = j.ManCoDocId
left join UserAuthorisedView k on a.JobID = k.JobID
inner join UserDocTypeView l on b.DocumentTypeID = l.DocumentTypeID
where b.ManCoDocID in (select manCoDocId from @tablevar)
and l.UserId = @UserId and ae.Name=@AppEnvironment
order by a.JobID desc


end
GO
/****** Object:  StoredProcedure [dbo].[GetJobsByJobReference]    Script Date: 21/04/2015 11:34:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE Procedure [dbo].[GetJobsByJobReference]

@SearchCriteria varchar(50)

as

Begin

select distinct c.ManCoShortName Company, d.DocumentTypeName Document, b.[Version], a.SubmissionDate SubmitDateTime, f.JobStatusDescription [Status], h.UserName [Owner],
b.EnvironmentId EnvironmentId, b.ManCoDocID, b.DocumentTypeID, a.JobID JobId, g.Hold HoldAuthorisation, d.BravuraDocTypeCode DocTypeCode, 
c.ManCoCode, SUBSTRING(i.EnvironmentType, 1, 1) EnvType
from Job a inner join ManCoDocs b on a.ManCoDocID = b.ManCoDocID
inner join ManagementCompany c on b.ManCoID = c.ManCoID
inner join DocumentType d on b.DocumentTypeID = d.DocumentTypeID
inner join JobStatus e on e.JobID = a.JobID
inner join JobStatusType f on f.JobStatusTypeID = e.JobStatusTypeID
inner join MediaDefinition g on g.ManCoDocID = b.ManCoDocID
inner join AspNetUsers h on h.Id = a.UserID
inner join EnvironmentType i on i.EnvironmentId = b.EnvironmentId
where d.DocumentTypeName like '%' + @SearchCriteria + '%' 
or c.ManCoShortName like '%' + @SearchCriteria + '%'
or f.JobStatusDescription like '%' + @SearchCriteria + '%'
or h.UserName like '%' + @SearchCriteria + '%'
or b.[Version] like '%' + @SearchCriteria + '%'

end

GO
/****** Object:  StoredProcedure [dbo].[GetJobsBySearchCriteria]    Script Date: 21/04/2015 11:34:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE Procedure [dbo].[GetJobsBySearchCriteria]

@UserId varchar(100),
@SearchCriteria varchar(50),
@AppEnvironment varchar(128),
@SearchCriteriaForDocument varchar(50)=NULL


as

Begin

Declare @tablevar table(manCoDocId int)

insert into @tablevar Exec GetManCoDocIds @UserId

select distinct i.BravuraDOCSEnvironmentChar + c.ManCoCode + d.BravuraDocTypeCode + rtrim(REPLICATE('0',8 - LEN(a.JobID)) + cast(a.JobID as varchar)) JobReference,
c.ManCoShortName Company, a.GRID, d.DocumentTypeName Document, b.[Version], CONVERT(CHAR(10), a.SubmissionDate, 103) +' '+ CONVERT(CHAR(9), a.SubmissionDate, 108) as SubmitDateTime, f.JobStatusDescription [Status], h.FirstName +' '+ h.LastName as [Owner],
b.EnvironmentID EnvironmentId, b.ManCoDocID, b.DocumentTypeID, a.JobID JobId, d.BravuraDocTypeCode DocTypeCode, 
c.ManCoCode, i.BravuraDOCSEnvironmentChar EnvType, b.Authorise ,b.ManualSubmissionOnly, j.JobAuthorise, k.FirstName+' ' +k.LastName Authoriser,
a.AllocatorGRID
from Job a inner join ManCoDocs b on a.ManCoDocID = b.ManCoDocID
inner join ManagementCompany c on b.ManCoID = c.ManCoID
inner join DocumentType d on b.DocumentTypeID = d.DocumentTypeID
inner join JobStatusType f on f.JobStatusTypeID = a.JobStatusTypeID
left join MediaDefinition g on g.ManCoDocID = b.ManCoDocID
inner join AspNetUsers h on h.Id = a.UserID
inner join EnvironmentType i on i.EnvironmentID = b.EnvironmentID
inner join AppEnvironment ae on ae.AppEnvironmentID=i.AppEnvironmentID
inner join JobAuthoriseView j on a.ManCoDocID = j.ManCoDocId
left join UserAuthorisedView k on a.JobID = k.JobID
inner join UserDocTypeView l on b.DocumentTypeID = l.DocumentTypeID
where (d.DocumentTypeName like '%' + @SearchCriteria + '%' 
or c.ManCoShortName like '%' + @SearchCriteria + '%'
or f.JobStatusDescription like '%' + @SearchCriteria + '%'
or h.UserName like '%' + @SearchCriteria + '%'
or h.FirstName like '%' + @SearchCriteria + '%'
or h.LastName like '%' + @SearchCriteria + '%'
or h.FirstName +' ' + h.LastName like '%' + @SearchCriteria + '%'
or b.[Version] like '%' + @SearchCriteria + '%'
or i.BravuraDOCSEnvironmentChar + c.ManCoCode + d.BravuraDocTypeCode + rtrim(REPLICATE('0',8 - LEN(a.JobID)) + cast(a.JobID as varchar)) like '%' + @SearchCriteria + '%'
or CONVERT(CHAR(10), a.SubmissionDate, 103) +' '+ CONVERT(CHAR(9), a.SubmissionDate, 108) like '%' + @SearchCriteria + '%'
or j.JobAuthorise like '%' + @SearchCriteria + '%'
or k.UserName like '%' + @SearchCriteria + '%'
or a.GRID like '%'+ @SearchCriteria +'%')
AND ((d.DocumentTypeName like '%' + @SearchCriteriaForDocument + '%') 
	OR (CONVERT(CHAR(10), a.SubmissionDate, 103) +' '+ CONVERT(CHAR(9), a.SubmissionDate, 108) like '%' + @SearchCriteriaForDocument + '%') 
	OR @SearchCriteriaForDocument IS NULL)
and (b.ManCoDocID in (select manCoDocId from @tablevar))
and l.UserId = @UserId and ae.Name=@AppEnvironment
order by a.JobID desc


end
GO
/****** Object:  StoredProcedure [dbo].[GetJobsByStatus]    Script Date: 21/04/2015 11:34:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create Procedure [dbo].[GetJobsByStatus]

@SearchCriteria varchar(50)

as

Begin

select distinct c.ManCoShortName Company, d.DocumentTypeName Document, b.[Version], a.SubmissionDate SubmitDateTime, f.JobStatusDescription [Status], h.UserName [Owner],
b.EnvntID EnvironmentId, b.ManCoDocID, b.DocumentTypeID, a.JobID JobId, g.Hold HoldAuthorisation, d.BravuraDocTypeCode DocTypeCode, 
c.ManCoCode, SUBSTRING(i.EnvironmentType, 1, 1) EnvType
from Job a inner join ManCoDocs b on a.ManCoDocID = b.ManCoDocID
inner join ManagementCompany c on b.ManCoID = c.ManCoID
inner join DocumentType d on b.DocumentTypeID = d.DocumentTypeID
inner join JobStatus e on e.JobID = a.JobID
inner join JobStatusType f on f.JobStatusTypeID = e.JobStatusTypeID
inner join MediaDefinition g on g.ManCoDocID = b.ManCoDocID
inner join AspNetUsers h on h.Id = a.UserID
inner join EnvironmentType i on i.EnvtID = b.EnvntID
where d.DocumentTypeName like '%' + @SearchCriteria + '%' 
or c.ManCoShortName like '%' + @SearchCriteria + '%'
or f.JobStatusDescription like '%' + @SearchCriteria + '%'
or h.UserName like '%' + @SearchCriteria + '%'
or b.[Version] like '%' + @SearchCriteria + '%'

end

GO
/****** Object:  StoredProcedure [dbo].[GetJobsByVersion]    Script Date: 21/04/2015 11:34:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create Procedure [dbo].[GetJobsByVersion]

@SearchCriteria varchar(50)

as

Begin

select distinct c.ManCoShortName Company, d.DocumentTypeName Document, b.[Version], a.SubmissionDate SubmitDateTime, f.JobStatusDescription [Status], h.UserName [Owner],
b.EnvntID EnvironmentId, b.ManCoDocID, b.DocumentTypeID, a.JobID JobId, g.Hold HoldAuthorisation, d.BravuraDocTypeCode DocTypeCode, 
c.ManCoCode, SUBSTRING(i.EnvironmentType, 1, 1) EnvType
from Job a inner join ManCoDocs b on a.ManCoDocID = b.ManCoDocID
inner join ManagementCompany c on b.ManCoID = c.ManCoID
inner join DocumentType d on b.DocumentTypeID = d.DocumentTypeID
inner join JobStatus e on e.JobID = a.JobID
inner join JobStatusType f on f.JobStatusTypeID = e.JobStatusTypeID
inner join MediaDefinition g on g.ManCoDocID = b.ManCoDocID
inner join AspNetUsers h on h.Id = a.UserID
inner join EnvironmentType i on i.EnvtID = b.EnvntID
where d.DocumentTypeName like '%' + @SearchCriteria + '%' 
or c.ManCoShortName like '%' + @SearchCriteria + '%'
or f.JobStatusDescription like '%' + @SearchCriteria + '%'
or h.UserName like '%' + @SearchCriteria + '%'
or b.[Version] like '%' + @SearchCriteria + '%'

end

GO
/****** Object:  StoredProcedure [dbo].[GetJobStatusType]    Script Date: 21/04/2015 11:34:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[GetJobStatusType]

@JobStatusDescription varchar(50)

as

Begin

select JobStatusTypeID, JobStatusDescription from JobStatusType

end
GO
/****** Object:  StoredProcedure [dbo].[GetJobStatusTypeByDescription]    Script Date: 21/04/2015 11:34:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create procedure [dbo].[GetJobStatusTypeByDescription]

@JobStatusDescription varchar(50)

as

Begin

select JobStatusTypeID, JobStatusDescription from JobStatusType where JobStatusDescription = @JobStatusDescription

end
GO
/****** Object:  StoredProcedure [dbo].[GetManCoDocByManCoDocTypeEnvironment]    Script Date: 21/04/2015 11:34:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[GetManCoDocByManCoDocTypeEnvironment]
	-- Add the parameters for the stored procedure here
	@ManCoCode nvarchar(50),
	@DocTypeCode nvarchar(50),
	@DocTypeName nvarchar(75),
	@Environment nvarchar(50)
	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT *
	FROM ManCoDocs 
		INNER JOIN ManagementCompany ON ManagementCompany.ManCoID = ManCoDocs.ManCoID 
		INNER JOIN DocumentType ON DocumentType.DocumentTypeID = ManCoDocs.DocumentTypeID
		INNER JOIN EnvironmentType ON EnvironmentType.EnvironmentID = ManCoDocs.EnvironmentID
		INNER JOIN AppEnvironment ON EnvironmentType.AppEnvironmentID = AppEnvironment.AppEnvironmentID
    WHERE 		
		ManagementCompany.ManCoCode = @ManCoCode AND
		DocumentType.BravuraDocTypeCode = @DocTypeCode AND
		DocumentType.DocumentTypeName = @DocTypeName AND
		AppEnvironment.Name = @Environment AND
		ManCoDocs.Active=1
END

GO
/****** Object:  StoredProcedure [dbo].[GetManCoDocIds]    Script Date: 21/04/2015 11:34:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create procedure [dbo].[GetManCoDocIds]

@UserId nvarchar(128)

as

begin

SET NOCOUNT ON;

SELECT DISTINCT ManCoDocs.ManCoDocID
	FROM UserGroups 
		INNER JOIN AspNetUsers ON UserGroups.UserId = AspNetUsers.Id 
		INNER JOIN [Group] ON UserGroups.GroupID = [Group].GroupID 
		INNER JOIN GroupDataRights ON [Group].GroupID = GroupDataRights.GroupID 
		INNER JOIN ManCoDocs ON GroupDataRights.ManCoDocID = ManCoDocs.ManCoDocID
		INNER JOIN ManagementCompany ON ManCoDocs.ManCoID = ManagementCompany.ManCoID
    WHERE AspNetUsers.Id = @UserId

end
GO
/****** Object:  StoredProcedure [dbo].[GetManCoDocsByManCoCodeEnvironment]    Script Date: 21/04/2015 11:34:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[GetManCoDocsByManCoCodeEnvironment]
	-- Add the parameters for the stored procedure here
	@ManCoCode nvarchar(50),	
	@Environment nvarchar(50),
	@UserId nvarchar(128)
	
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT md.ManCoDocID,md.BravuraDocID,md.ManCoID,md.DocumentTypeID,dt.DocumentTypeName+'_'+md.PubFileName as 'PubFileName'
	,md.EnvironmentID,md.Version,md.VersionComments,md.Active,md.Authorise,md.ManualSubmissionOnly,md.DateFolderCreation,md.ManCoDocURI
	FROM ManCoDocs md
		inner join DocumentType dt on dt.DocumentTypeID=md.DocumentTypeID
	    INNER JOIN GroupDataRights gdr on gdr.ManCoDocID=md.ManCoDocID
		INNER JOIN [Group] g on g.GroupID=gdr.GroupID
		INNER JOIN UserGroups ug on ug.GroupID=g.GroupID
		INNER JOIN AspNetUsers u ON u.Id=ug.UserId
		INNER JOIN ManagementCompany ON ManagementCompany.ManCoID = md.ManCoID 		
		INNER JOIN EnvironmentType ON EnvironmentType.EnvironmentID = md.EnvironmentID
		INNER JOIN AppEnvironment ON EnvironmentType.AppEnvironmentID = AppEnvironment.AppEnvironmentID
    WHERE 		
		ManagementCompany.ManCoCode = @ManCoCode AND	
		AppEnvironment.Name = @Environment AND
		md.Active=1 and u.Id=@UserId
END

GO
/****** Object:  StoredProcedure [dbo].[GetManCoIds]    Script Date: 21/04/2015 11:34:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[GetManCoIds]

@UserId nvarchar(128)

as

begin

SET NOCOUNT ON;

SELECT DISTINCT ManagementCompany.ManCoID
	FROM UserGroups 
		INNER JOIN AspNetUsers ON UserGroups.UserId = AspNetUsers.Id 
		INNER JOIN [Group] ON UserGroups.GroupID = [Group].GroupID 
		INNER JOIN GroupDataRights ON [Group].GroupID = GroupDataRights.GroupID 
		INNER JOIN ManCoDocs ON GroupDataRights.ManCoDocID = ManCoDocs.ManCoDocID
		INNER JOIN ManagementCompany ON ManCoDocs.ManCoID = ManagementCompany.ManCoID
    WHERE AspNetUsers.Id = @UserId

end
GO
/****** Object:  StoredProcedure [dbo].[GetManCos]    Script Date: 21/04/2015 11:34:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[GetManCos] 
	-- Add the parameters for the stored procedure here
	@UserId nvarchar(128)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT DISTINCT ManagementCompany.*
	FROM UserGroups 
		INNER JOIN AspNetUsers ON UserGroups.UserId = AspNetUsers.Id 
		INNER JOIN [Group] ON UserGroups.GroupID = [Group].GroupID 
		INNER JOIN GroupDataRights ON [Group].GroupID = GroupDataRights.GroupID 
		INNER JOIN ManCoDocs ON GroupDataRights.ManCoDocID = ManCoDocs.ManCoDocID
		INNER JOIN ManagementCompany ON ManCoDocs.ManCoID = ManagementCompany.ManCoID
    WHERE AspNetUsers.Id = @UserId
    ORDER By ManagementCompany.ManCoName
END

GO
/****** Object:  StoredProcedure [dbo].[GetMediaDefinitions]    Script Date: 21/04/2015 11:34:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[GetMediaDefinitions] 
	-- Add the parameters for the stored procedure here
	@IndexDefinitionID int
AS
BEGIN

SELECT [MediaDefID]
      ,[QueueName]
      ,[QueueNameSymbollic]
      ,[ManCoDocID]
      ,[IndexDefinitionID]
      ,[DefinitionName]
      ,[Hold]
      ,[BatchSizeSheets]
      ,[MailsortSettingID]
      ,[FinishingDefID]
      ,[SortID]
      ,[PDF_Only]
      ,[createDate]
      ,[createUserID]
  FROM [nexdox_BBOS].[dbo].[MediaDefinition]
  where IndexDefinitionID = @IndexDefinitionID
  
 END
GO
/****** Object:  StoredProcedure [dbo].[GetMediaDefinitionsByManCoDoc]    Script Date: 21/04/2015 11:34:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[GetMediaDefinitionsByManCoDoc] 
	@ManCoDocID int
AS
BEGIN

	SELECT MediaDefID
		  ,QueueName
		  ,QueueNameSymbollic
		  ,m.ManCoDocID
		  ,m.IndexDefinitionID
		  ,QueueDescription
		  ,Hold
		  ,BatchSizeSheets
		  ,FinishingDefID
		  ,SortID
		  ,[Print]
		  ,PDF_Only
		  ,createDate
		  ,createUserID
		  ,OutputFilename
		  ,OldQueueName
		  ,ReportHPExstreamSymbolicName
		  ,m.ReportLayoutFileID
		  ,m.ReportIsArchive
		  ,ReturnReportInDJA
		  ,ReturnOutput
		  ,ShowOnShortReport
		  ,ShowPDFOnWebPortal
		  ,ShowReportOnWebPortal
		  ,IsCD
		  ,Colour
		  ,EncloseEnvelope
		  ,[Action]
		  ,IndexDefinitionName
		  ,ExstreamSymbolicIndexName
		  ,ExstreamSymbolicDataName
		  ,ExstreamSymbolicReportName
		  ,IndexDescription
		  ,i.ReportIsArchive
		  ,i.MailSortSettingID
	  FROM dbo.MediaDefinition m
	  INNER JOIN dbo.IndexDefinition i ON i.IndexDefinitionID = m.IndexDefinitionID
	  WHERE m.ManCoDocID = @ManCoDocID
END
GO
/****** Object:  StoredProcedure [dbo].[GetMediaStockByMediaDefinition]    Script Date: 21/04/2015 11:34:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[GetMediaStockByMediaDefinition] 
	@MediaDefinitionId int
AS
BEGIN

	SELECT Stock.StockId, Code, [Description], [Weight], SizeX, SizeY, Facing, Colour, Stock.StockTypeId, StockType, MediaStockId, MediaDefId, PublishDate, StationNumber
	FROM dbo.MediaStock
	INNER JOIN dbo.Stock ON MediaStock.StockID = Stock.StockID
	INNER JOIN dbo.StockType ON StockType.StockTypeID = Stock.StockTypeID
	WHERE mediadefid = @MediaDefinitionId
	ORDER BY StockTypeID, StationNumber

END
GO
/****** Object:  StoredProcedure [dbo].[GetStockByType]    Script Date: 21/04/2015 11:34:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[GetStockByType] 
	@StockType VARCHAR(50),
	@Filter VARCHAR(255)
AS

SELECT s.StockId, RTRIM(s.Code) [Code], s.[Description], s.[Weight], s.SizeX, s.SizeY, s.Facing, s.Colour
FROM dbo.Stock [s]
INNER JOIN dbo.StockType [st] ON st.StockTypeID = s.StockTypeID
WHERE st.StockType = @StockType AND s.[Description] LIKE @Filter
ORDER BY s.Code, s.[Description]
GO
/****** Object:  StoredProcedure [dbo].[GetUsersByEnvironment]    Script Date: 21/04/2015 11:34:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[GetUsersByEnvironment]
	@AppEnvironment varchar(128)
AS
BEGIN
	
	SET NOCOUNT ON;

    
	SELECT distinct AspNetUsers.*
	FROM UserGroups 
		INNER JOIN AspNetUsers ON UserGroups.UserId = AspNetUsers.Id 
		INNER JOIN [Group] ON UserGroups.GroupID = [Group].GroupID 
		INNER JOIN GroupDataRights ON [Group].GroupID = GroupDataRights.GroupID 
		INNER JOIN AppEnvironment ON GroupDataRights.EnvironmentID = AppEnvironment.AppEnvironmentID
    WHERE AppEnvironment.Name = @AppEnvironment
    ORDER BY AspNetUsers.UserName
END

GO
/****** Object:  StoredProcedure [dbo].[GetUsersBySearchCriteria]    Script Date: 21/04/2015 11:34:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[GetUsersBySearchCriteria]
	@SearchCriteria varchar(50),
	@AppEnvironment varchar(128)
AS
BEGIN
	
	SET NOCOUNT ON;

    
	SELECT distinct AspNetUsers.*
	FROM UserGroups 
		INNER JOIN AspNetUsers ON UserGroups.UserId = AspNetUsers.Id 
		INNER JOIN [Group] ON UserGroups.GroupID = [Group].GroupID 
		INNER JOIN GroupDataRights ON [Group].GroupID = GroupDataRights.GroupID 
		INNER JOIN AppEnvironment ON GroupDataRights.EnvironmentID = AppEnvironment.AppEnvironmentID
    WHERE AppEnvironment.Name = @AppEnvironment
		AND (AspNetUsers.FirstName like '%' + @SearchCriteria + '%' 
			OR AspNetUsers.LastName like '%' + @SearchCriteria + '%' 
			OR AspNetUsers.UserName like '%' + @SearchCriteria + '%')
    ORDER BY AspNetUsers.UserName
END

GO
/****** Object:  StoredProcedure [dbo].[InsertJobStatus_Deleted]    Script Date: 21/04/2015 11:34:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Create procedure [dbo].[InsertJobStatus_Deleted]

@JobId int,
@JobStatusTypeId int

as

Begin

Insert JobStatus (JobID, JobStatusTypeID, [DateTime]) values (@JobId, @JobStatusTypeId, GETDATE())

End
GO
/****** Object:  StoredProcedure [dbo].[nm_bulkInsertCDReport]    Script Date: 21/04/2015 11:34:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- Batch submitted through debugger: SQLQuery76.sql|7|0|C:\Users\JEREMY~1.WHI\AppData\Local\Temp\~vsD82A.sql
-- ===============================================
-- Author:		Jeremy Whittaker
-- Create date: 30/09/2014
-- Description:	bulk insert data file to use when 
--				creating cd trg files
-- ===============================================

CREATE proc [dbo].[nm_bulkInsertCDReport]
(
	@Filename as varchar(500),
	@Environment as char(1),
	@GRID as varchar(20)
)
as
begin

	declare @sql as varchar(5000)

	-- drop table if previously exists
	if exists (select * from dbo.sysobjects where id = object_id(N'[' + @Environment + '_tmpCD_Data_' + @GRID + ']') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
		begin
			set @sql = 'drop table [' + @Environment + '_tmpCD_Data_' + @GRID + ']'
			exec(@sql)
		end

	-- create table to load data file into
	set @sql = 'create table [' + @Environment + '_tmpCD_Data_' + @GRID + '] 
	(
		agentcode varchar(10),
		InvestorCode varchar(10),
		InvestorName varchar(50),
		fundType varchar(150),
		fundName nvarchar(250),
		shareValue nvarchar(30),
		noOfshares nvarchar(30),
		sequenceid varchar(10)
	)'
	exec(@sql)

	--bulk insert data
	set @sql = 'BULK INSERT [' + @Environment + '_tmpCD_Data_' + @GRID + '] 
					from ' + char(39) + @Filename + char(39) +'
					WITH
						(
						FIELDTERMINATOR = ' + char(39) + '~' + char(39) +',
						ROWTERMINATOR = ' + char(39) + '\n' + char(39) +',
						CODEPAGE = ' + char(39) + 'ACP' + char(39) + ')'
	--print @sql
	exec(@sql)

	-- alter table to auto incremnent identity
	set @sql = 'alter table [' + @Environment + '_tmpCD_Data_' + @GRID + '] add cdid int identity(1,1)'
	exec(@sql)

	-- add index
	set @sql = 'create index [idx_' + @Environment + '_tmpCD_Data_' + @GRID + '_sequenceid] on [' + @Environment + '_tmpCD_Data_' + @GRID + '] (sequenceid)'
	exec(@sql)

	-- update number of shares to have only 3 decimal places (so truncating so as not to get any rounding issues)
	set @sql = 'update [' + @Environment + '_tmpCD_Data_' + @GRID + '] set noOfShares = 
					case when len(substring(noofshares, charindex(' + char(39) + '.' + char(39) + ', noOfShares), len(noofshares))) > 4
						 then substring(ltrim(rtrim(noofshares)), 1, len(ltrim(rtrim(noofshares)))-
							  (len(substring(noofshares, charindex(' + char(39) + '.' + char(39) + ', noOfShares), len(noofshares)))-4))
						 else noOfShares
						 end'
	exec(@sql)
end

GO
/****** Object:  StoredProcedure [dbo].[nm_bulkInsertCDReport_v1.0]    Script Date: 21/04/2015 11:34:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- Batch submitted through debugger: SQLQuery76.sql|7|0|C:\Users\JEREMY~1.WHI\AppData\Local\Temp\~vsD82A.sql
-- ===============================================
-- Author:		Jeremy Whittaker
-- Create date: 30/09/2014
-- Description:	bulk insert data file to use when 
--				creating cd trg files
-- ===============================================

create proc [dbo].[nm_bulkInsertCDReport_v1.0]
(
	@Filename as varchar(500),
	@Environment as char(1),
	@GRID as varchar(20)
)
as
begin

	declare @sql as varchar(5000)

	-- drop table if previously exists
	if exists (select * from dbo.sysobjects where id = object_id(N'[' + @Environment + '_tmpCD_Data_' + @GRID + ']') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
		begin
			set @sql = 'drop table [' + @Environment + '_tmpCD_Data_' + @GRID + ']'
			exec(@sql)
		end

	-- create table to load data file into
	set @sql = 'create table [' + @Environment + '_tmpCD_Data_' + @GRID + '] 
	(
		agentcode varchar(10),
		InvestorCode varchar(10),
		InvestorName varchar(50),
		fundType varchar(150),
		fundName nvarchar(250),
		shareValue nvarchar(30),
		noOfshares nvarchar(30),
		sequenceid varchar(10)
	)'
	exec(@sql)

	--bulk insert data
	set @sql = 'BULK INSERT [' + @Environment + '_tmpCD_Data_' + @GRID + '] 
					from ' + char(39) + @Filename + char(39) +'
					WITH
						(
						FIELDTERMINATOR = ' + char(39) + '~' + char(39) +',
						ROWTERMINATOR = ' + char(39) + '\n' + char(39) +',
						CODEPAGE = ' + char(39) + 'ACP' + char(39) + ')'
	--print @sql
	exec(@sql)

	-- alter table to auto incremnent identity
	set @sql = 'alter table [' + @Environment + '_tmpCD_Data_' + @GRID + '] add cdid int identity(1,1)'
	exec(@sql)

	-- add index
	set @sql = 'create index [idx_' + @Environment + '_tmpCD_Data_' + @GRID + '_sequenceid] on [' + @Environment + '_tmpCD_Data_' + @GRID + '] (sequenceid)'
	exec(@sql)



end

GO
/****** Object:  StoredProcedure [dbo].[nm_CheckStatementDocCode]    Script Date: 21/04/2015 11:34:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [dbo].[nm_CheckStatementDocCode]
(
	@RufusDBCode as char(3),
	@DocTypeCode as char(3),
	@Environment as int,
	@IsAgent as int
)
as

begin

-- check if the combination exists for that management company
-- this is specific on statements where it could be GTA / STA / STI / MVI / MVA
-- and it can't be reliably derived from the filename (at the moment)

select 
	mc.ManCoCode, mc.RufusDatabaseID, dt.BravuraDocTypeCode, dt.AgentDocument, md.EnvironmentID 
from 
	ManagementCompany mc
inner join
	ManCoDocs md on md.ManCoID = mc.ManCoID
inner join
	DocumentType dt on dt.DocumentTypeID = md.DocumentTypeID
where
		RufusDatabaseID		= @RufusDBCode 
	and BravuraDocTypeCode	= @DocTypeCode
	and md.EnvironmentID	= @Environment
	and dt.AgentDocument	= @IsAgent
	and md.Active = 1

end

GO
/****** Object:  StoredProcedure [dbo].[nm_CheckStatementDocCode_v1.0]    Script Date: 21/04/2015 11:34:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create proc [dbo].[nm_CheckStatementDocCode_v1.0]
(
	@RufusDBCode as char(3),
	@DocTypeCode as char(3),
	@Environment as int,
	@IsAgent as int
)
as

begin

-- check if the combination exists for that management company
-- this is specific on statements where it could be GTA / STA / STI / MVI / MVA
-- and it can't be reliably derived from the filename (at the moment)

select 
	mc.ManCoCode, mc.RufusDatabaseID, dt.BravuraDocTypeCode, dt.AgentDocument, md.EnvironmentID 
from 
	ManagementCompany mc
inner join
	ManCoDocs md on md.ManCoID = mc.ManCoID
inner join
	DocumentType dt on dt.DocumentTypeID = md.DocumentTypeID
where
		RufusDatabaseID		= @RufusDBCode 
	and BravuraDocTypeCode	= @DocTypeCode
	and md.EnvironmentID	= @Environment
	and dt.AgentDocument	= @IsAgent
	

end

GO
/****** Object:  StoredProcedure [dbo].[nm_DropCDbulkInsertTable]    Script Date: 21/04/2015 11:34:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create proc [dbo].[nm_DropCDbulkInsertTable]
(
	@Environment as char(1),
	@GRID as varchar(20)
)
as
begin

	declare @sql varchar(2000)

	if exists (select * from dbo.sysobjects where id = object_id(N'[' + @Environment + '_tmpCD_Data_' + @GRID + ']') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
		begin
			set @sql = 'drop table [' + @Environment + '_tmpCD_Data_' + @GRID + ']'
			exec(@sql)
		end

end
GO
/****** Object:  StoredProcedure [dbo].[nm_GetAdditionalSetup]    Script Date: 21/04/2015 11:34:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[nm_GetAdditionalSetup] 
	
	@ManCoCode varchar(2),
	@DocumentTypeCode varchar(3),
	@Agent bit,
	@Environment int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	
	SELECT 
		dt.AdditionalSetup
	FROM 
		[ManCoDocs] as mcd
	inner join 
		ManagementCompany mc on mc.ManCoID = mcd.ManCoID
	inner join 
		DocumentType dt on dt.DocumentTypeID = mcd.DocumentTypeID
	where 
		mc.ManCoCode = @ManCoCode 
	and 
		dt.BravuraDocTypeCode = @DocumentTypeCode 
	and 
		dt.AgentDocument = @Agent 
	and 
		mcd.EnvironmentID = @Environment		
	and
		mcd.Active = 1		
END

GO
/****** Object:  StoredProcedure [dbo].[nm_GetAdditionalSetup_v1.0]    Script Date: 21/04/2015 11:34:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
create PROCEDURE [dbo].[nm_GetAdditionalSetup_v1.0] 
	
	@ManCoCode varchar(2),
	@DocumentTypeCode varchar(3),
	@Agent bit,
	@Environment int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	
	SELECT 
		dt.AdditionalSetup
	FROM 
		[ManCoDocs] as mcd
	inner join 
		ManagementCompany mc on mc.ManCoID = mcd.ManCoID
	inner join 
		DocumentType dt on dt.DocumentTypeID = mcd.DocumentTypeID
	where 
		mc.ManCoCode = @ManCoCode 
	and 
		dt.BravuraDocTypeCode = @DocumentTypeCode 
	and 
		dt.AgentDocument = @Agent 
	and 
		mcd.EnvironmentID = @Environment		
		
END

GO
/****** Object:  StoredProcedure [dbo].[nm_GetAdditionalSetupByDocCode]    Script Date: 21/04/2015 11:34:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[nm_GetAdditionalSetupByDocCode] 
	
	@ManCoCode varchar(2),
	@DocumentTypeCode varchar(3),
	@Agent bit,
	@Environment int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	
	SELECT 
		*
	FROM 
		[ManCoDocs] as mcd
	inner join 
		ManagementCompany mc on mc.ManCoID = mcd.ManCoID
	inner join 
		DocumentType dt on dt.DocumentTypeID = mcd.DocumentTypeID
	where 
		mc.ManCoCode = @ManCoCode 
	and 
		dt.BravuraDocTypeCode = @DocumentTypeCode 
	and 
		dt.AgentDocument = @Agent 
	and 
		mcd.EnvironmentID = @Environment		
		
END

GO
/****** Object:  StoredProcedure [dbo].[nm_GetEnvironmentServers]    Script Date: 21/04/2015 11:34:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create Procedure [dbo].[nm_GetEnvironmentServers]

@EnvironmentType varchar(50)

as

begin

select b.ProcessingServerName ServerName, b.EnvironmentType, b.EnvironmentID EnvironmentTypeId
from AppEnvironment a inner join EnvironmentType b
on a.AppEnvironmentID = b.AppEnvironmentID
where a.Name = @EnvironmentType

end

GO
/****** Object:  StoredProcedure [dbo].[nm_GetManCoByRufusDatabaseID]    Script Date: 21/04/2015 11:34:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE proc [dbo].[nm_GetManCoByRufusDatabaseID]
(
	@FileExtension AS VARCHAR(10)
)
as
Begin

	-- return management company code for development jobs querying management company table & rufus UAT codes table
	WITH rDBCodes (RufusDatabaseCode, ManCode, Active) as 
	(
		select 
			RufusDatabaseID, mc.ManCoCode, Active
		from 
			ManagementCompany mc
		Union
		SELECT 
			RufusDBCode, mc.ManCoCode, Active
		FROM 
			[RufusDBCodes] rd
		INNER JOIN 
			dbo.ManagementCompany mc ON mc.ManCoID = rd.ManCoID
	)
	Select ManCode from rDBCodes WHERE RufusDatabaseCode = @FileExtension and Active =1;

End
GO
/****** Object:  StoredProcedure [dbo].[nm_GetManCoByRufusDatabaseID_v1.0]    Script Date: 21/04/2015 11:34:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
create PROCEDURE [dbo].[nm_GetManCoByRufusDatabaseID_v1.0] 
	-- Add the parameters for the stored procedure here
	@RufusDatabseID varchar(3)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT ManCoCode from ManagementCompany where RufusDatabaseID = @RufusDatabseID
	
END

GO
/****** Object:  StoredProcedure [dbo].[nm_GetManCoByRufusDatabaseID_v1.1]    Script Date: 21/04/2015 11:34:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
create PROCEDURE [dbo].[nm_GetManCoByRufusDatabaseID_v1.1] 
	-- Add the parameters for the stored procedure here
	@RufusDatabseID varchar(3)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT 
		ManCoCode 
	from
		ManagementCompany 
	where 
		RufusDatabaseID = @RufusDatabseID
		and Active = 1
	
END

GO
/****** Object:  StoredProcedure [dbo].[nm_GetManualSubmission]    Script Date: 21/04/2015 11:34:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[nm_GetManualSubmission] 
	-- Add the parameters for the stored procedure here
	@ManCo varchar(2),
	@DocTypeID varchar(3),
	@Agent bit,
	@EnvironmentID varchar(1)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	SELECT  
		isnull(ManualSubmissionOnly, 0)
	FROM 
		ManCoDocs md
	INNER JOIN
		ManagementCompany mc on	mc.ManCoID = md.ManCoID
	INNER JOIN 
		DocumentType dt on dt.DocumentTypeID = md.DocumentTypeID
	WHERE
		mc.ManCoCode = @ManCo and 
		dt.BravuraDocTypeCode = @DocTypeID and 
		dt.AgentDocument = @Agent and 
		md.EnvironmentID = @EnvironmentID and
		md.Active = 1
END

GO
/****** Object:  StoredProcedure [dbo].[nm_GetManualSubmission_v1.0]    Script Date: 21/04/2015 11:34:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[nm_GetManualSubmission_v1.0] 
	-- Add the parameters for the stored procedure here
	@ManCo varchar(2),
	@DocTypeID varchar(3),
	@Agent bit,
	@EnvironmentID varchar(1)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	SELECT  
		isnull(ManualSubmissionOnly, 0)
	FROM 
		ManCoDocs md
	INNER JOIN
		ManagementCompany mc on	mc.ManCoID = md.ManCoID
	INNER JOIN 
		DocumentType dt on dt.DocumentTypeID = md.DocumentTypeID
	WHERE
		mc.ManCoCode = @ManCo and 
		dt.BravuraDocTypeCode = @DocTypeID and 
		dt.AgentDocument = @Agent and 
		md.EnvironmentID = @EnvironmentID 
		
END

GO
/****** Object:  StoredProcedure [dbo].[nm_MailsortSwapIdnexDefIds]    Script Date: 21/04/2015 11:34:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [dbo].[nm_MailsortSwapIdnexDefIds]
(
	 @ManCoDocID AS INT
)

AS
Begin

--SET @ManCoDocID = 61

	DECLARE @HoldSwapIDs TABLE
	(
	MediaDefID INT,
	SortID INT
	)

	-- store original values
	INSERT INTO @HoldSwapIDs
			( MediaDefID, SortID )
	 SELECT  mediadefid, sortid FROM dbo.MediaDefinition 
	WHERE ManCoDocID = @ManCoDocID AND (QueueName LIKE '%DOMESTIC_AUTO' OR QueueName LIKE '%DOMESTIC_MANUAL')

	-- swap IDs
	UPDATE dbo.MediaDefinition SET SortID = IndexDefinitionid FROM dbo.MediaDefinition 
	WHERE ManCoDocID = @ManCoDocID AND (QueueName LIKE '%DOMESTIC_AUTO' OR QueueName LIKE '%DOMESTIC_MANUAL')

	UPDATE dbo.MediaDefinition SET IndexDefinitionID = hs.SortID FROM dbo.MediaDefinition md
	INNER JOIN @HoldSwapIDs hs ON hs.MediaDefID = md.MediaDefID
	WHERE ManCoDocID = @ManCoDocID AND (QueueName LIKE '%DOMESTIC_AUTO' OR QueueName LIKE '%DOMESTIC_MANUAL')

END
GO
/****** Object:  StoredProcedure [dbo].[nm_SelAgentFromJobID]    Script Date: 21/04/2015 11:34:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create proc [dbo].[nm_SelAgentFromJobID]
(
	@Jobid int
)
as
begin

-- select agent document flag using the jobid passed in
	select 
		AgentDocument 
	from 
		DocumentType dt
	inner join 
		ManCoDocs md on md.DocumentTypeID = dt.DocumentTypeID
	inner join 
		Job jb on jb.ManCoDocID = md.ManCoDocID
	where 
		jb.JobID = @Jobid

end
GO
/****** Object:  StoredProcedure [dbo].[nm_SelDateFolderCreation]    Script Date: 21/04/2015 11:34:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Jeremy Whittaker>
-- Create date: <14/07/2014>
-- Description:	<Select the DateFolderCreation value for a specific manco/doc/agent>
-- =============================================
CREATE PROCEDURE [dbo].[nm_SelDateFolderCreation] 
	
	@ManCoCode varchar(2),
	@DocumentTypeCode varchar(3),
	@Agent bit,
	@Environment int

AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	
	SELECT 
		mcd.DateFolderCreation
	FROM 
		[ManCoDocs] as mcd
	inner join 
		ManagementCompany mc on mc.ManCoID = mcd.ManCoID
	inner join 
		DocumentType dt on dt.DocumentTypeID = mcd.DocumentTypeID
	where 
		mc.ManCoCode = @ManCoCode 
	and 
		dt.BravuraDocTypeCode = @DocumentTypeCode 
	and 
		dt.AgentDocument = @Agent 
	and 
		mcd.EnvironmentID = @Environment	
	and 
		mcd.Active = 1
END

GO
/****** Object:  StoredProcedure [dbo].[nm_SelDateFolderCreation_v1.0]    Script Date: 21/04/2015 11:34:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Jeremy Whittaker>
-- Create date: <14/07/2014>
-- Description:	<Select the DateFolderCreation value for a specific manco/doc/agent>
-- =============================================
create PROCEDURE [dbo].[nm_SelDateFolderCreation_v1.0] 
	
	@ManCoCode varchar(2),
	@DocumentTypeCode varchar(3),
	@Agent bit

AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	
	SELECT 
		mcd.DateFolderCreation
	FROM 
		[ManCoDocs] as mcd
	inner join 
		ManagementCompany mc on mc.ManCoID = mcd.ManCoID
	inner join 
		DocumentType dt on dt.DocumentTypeID = mcd.DocumentTypeID
	where 
		mc.ManCoCode = @ManCoCode 
	and 
		dt.BravuraDocTypeCode = @DocumentTypeCode 
	and 
		dt.AgentDocument = @Agent 
	
		
END

GO
/****** Object:  StoredProcedure [dbo].[nm_SelDateTimeStamp]    Script Date: 21/04/2015 11:34:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Rob Gunning>
-- Create date: <09/01/2015>
-- Description:	<Select the DateTimeStamp value for a specific manco/doc/agent>
-- =============================================
CREATE PROCEDURE [dbo].[nm_SelDateTimeStamp] 
	
	@ManCoCode varchar(2),
	@DocumentTypeCode varchar(3),
	@Agent bit,
	@Environment int

AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	
	SELECT 
		mcd.DateTimeStampInputFiles
	FROM 
		[ManCoDocs] as mcd
	inner join 
		ManagementCompany mc on mc.ManCoID = mcd.ManCoID
	inner join 
		DocumentType dt on dt.DocumentTypeID = mcd.DocumentTypeID
	where 
		mc.ManCoCode = @ManCoCode 
	and 
		dt.BravuraDocTypeCode = @DocumentTypeCode 
	and 
		dt.AgentDocument = @Agent 
	and 
		mcd.EnvironmentID = @Environment	
	and 
		mcd.Active = 1
END

GO
/****** Object:  StoredProcedure [dbo].[nm_SelDynamicReferenceExists]    Script Date: 21/04/2015 11:34:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create proc [dbo].[nm_SelDynamicReferenceExists]
(
	@ManCoCode varchar(2),
	@DocumentTypeCode varchar(3),
	@Agent bit,
	@Environment int
)

AS
BEGIN

	/*set @ManCoCode = 'SL'
	set @DocumentTypeCode = 'CNT'
	set @Agent = 0
	set @Environment = 1*/
	
	-- return a record if the ManCoDoc uses a RUFUS_EXTRACT
	SELECT
		charindex('RUFUS_EXTRACT', hp.HPExstreamSymbolicName) 
	FROM
		HPExstreamReferenceFiles hp 
	INNER JOIN 
		(SELECT 
				mcd.ManCoDocID
			FROM 
				[ManCoDocs] as mcd
			inner join 
				ManagementCompany mc on mc.ManCoID = mcd.ManCoID
			inner join 
				DocumentType dt on dt.DocumentTypeID = mcd.DocumentTypeID
			where 
				mc.ManCoCode = @ManCoCode 
			and 
				dt.BravuraDocTypeCode = @DocumentTypeCode 
			and 
				dt.AgentDocument = @Agent 
			and 
				mcd.EnvironmentID = @Environment		
			and
				mcd.Active = 1)  mc 
		ON 
			mc.ManCoDocID = hp.ManCoDocID
		WHERE 
			hp.HPExstreamSymbolicName like '%RUFUS_EXTRACT%'
			
END		
GO
/****** Object:  StoredProcedure [dbo].[nm_SelectCDrecords]    Script Date: 21/04/2015 11:34:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE proc [dbo].[nm_SelectCDrecords]
(
	@Environment as char(1),
	@GRID as varchar(20),
	@sequenceid as varchar(10)
)
as
begin

	declare @sql varchar(2000)

	if exists (select * from dbo.sysobjects where id = object_id(N'[' + @Environment + '_tmpCD_Data_' + @GRID + ']') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
		begin
			set @sql = 'select * from [' + @Environment + '_tmpCD_Data_' + @GRID + '] where sequenceID = ' + @sequenceid + ' order by cdid'
			exec(@sql)
		end

end

GO
/****** Object:  StoredProcedure [dbo].[nm_SelectLGCDrecords]    Script Date: 21/04/2015 11:34:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE proc [dbo].[nm_SelectLGCDrecords]
(
	@Environment as char(1),
	@GRID as varchar(20),
	@sequenceid as varchar(10)
)
as
begin

	declare @sql varchar(2000)

	if exists (select * from dbo.sysobjects where id = object_id(N'[' + @Environment + '_tmpCD_Data_' + @GRID + ']') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
		begin
			set @sql = 'select * from [' + @Environment + '_tmpCD_Data_' + @GRID + '] where AgentCode = ' + @sequenceid + ' order by cdid'
			exec(@sql)
		end

end

GO
/****** Object:  StoredProcedure [dbo].[nm_SelRufusCodeExists]    Script Date: 21/04/2015 11:34:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [dbo].[nm_SelRufusCodeExists]
(
	@rufusCode as char(3)
)
as

begin

-- return the rufus db code from the statement file extension
select top 1
	mc.RufusDatabaseID
from 
	ManagementCompany mc
where
	RufusDatabaseID	= @rufusCode 
	and Active = 1
	
end

GO
/****** Object:  StoredProcedure [dbo].[nm_SelRufusCodeExists_v1.0]    Script Date: 21/04/2015 11:34:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create proc [dbo].[nm_SelRufusCodeExists_v1.0]
(
	@rufusCode as char(3)
)
as

begin

-- return the rufus db code from the statement file extension
select top 1
	mc.RufusDatabaseID
from 
	ManagementCompany mc
where
	RufusDatabaseID	= @rufusCode 
	
	
end

GO
/****** Object:  StoredProcedure [dbo].[nm_SelRufusCodeWithFileExtension]    Script Date: 21/04/2015 11:34:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [dbo].[nm_SelRufusCodeWithFileExtension]
(
	@Extension as char(3)
)
as

begin

-- return the rufus db code from the statement file extension
select top 1
	mc.RufusDatabaseID
from 
	ManagementCompany mc
INNER JOIN 
	dbo.RufusDBCodes rd 
ON 
	rd.ManCoID = mc.ManCoID
where
	(StatementFileExtension	= @Extension 
	OR rd.RufusDBCode = @Extension)
	and Active = 1
	
end

GO
/****** Object:  StoredProcedure [dbo].[nm_SelRufusCodeWithFileExtension_v1.0]    Script Date: 21/04/2015 11:34:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create proc [dbo].[nm_SelRufusCodeWithFileExtension_v1.0]
(
	@Extension as char(3)
)
as

begin

-- return the rufus db code from the statement file extension
select top 1
	mc.RufusDatabaseID
from 
	ManagementCompany mc
where
	StatementFileExtension	= @Extension 

end

GO
/****** Object:  StoredProcedure [dbo].[nm_SelRufusCodeWithFileExtension_v1.1]    Script Date: 21/04/2015 11:34:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create proc [dbo].[nm_SelRufusCodeWithFileExtension_v1.1]
(
	@Extension as char(3)
)
as

begin

-- return the rufus db code from the statement file extension
select top 1
	mc.RufusDatabaseID
from 
	ManagementCompany mc
where
	StatementFileExtension	= @Extension 
	and Active = 1
	
end

GO
/****** Object:  StoredProcedure [dbo].[nm_SelRufusCodeWithFileExtension_v1_2]    Script Date: 21/04/2015 11:34:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE proc [dbo].[nm_SelRufusCodeWithFileExtension_v1_2]
(
	@Extension as char(3)
)
as

begin

-- return the rufus db code from the statement file extension
select top 1
	mc.RufusDatabaseID
from 
	ManagementCompany mc
INNER JOIN 
	dbo.RufusDBCodes rd 
ON 
	rd.ManCoID = mc.ManCoID
where
	(StatementFileExtension	= @Extension 
	OR rd.RufusDBCode = @Extension)
	and Active = 1
	
end

GO
/****** Object:  StoredProcedure [dbo].[nm_selRufusDatabaseID]    Script Date: 21/04/2015 11:34:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROC [dbo].[nm_selRufusDatabaseID]
(
	@FileExtension AS VARCHAR(10),
	@EnvironmentID AS INT
)
AS
Begin


SELECT 
	mc.ManCoCode
FROM 
	[RufusDBCodes] rd
INNER JOIN 
	dbo.ManagementCompany mc ON mc.ManCoID = rd.ManCoID
WHERE 
	RufusDBCode = @FileExtension AND EnvironmentID = @EnvironmentID
End
GO
/****** Object:  StoredProcedure [dbo].[nmBBOSSummaryGroupDataRights]    Script Date: 21/04/2015 11:34:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Asa Burrows>
-- Create date: <05/11/2014>
-- Description:	<BBOS Group Data Rights Report>
-- =============================================
CREATE PROCEDURE [dbo].[nmBBOSSummaryGroupDataRights]
	-- No parameters
AS
BEGIN

SELECT DISTINCT 
                         [Group].GroupName, ManagementCompany.ManCoCode, ManagementCompany.ManCoName, DocumentType.BravuraDocTypeCode, 
                         EnvironmentType.BravuraDOCSEnvironmentType, ManCoDocs.VersionComments
FROM            [Group] INNER JOIN
                         GroupDataRights ON [Group].GroupID = GroupDataRights.GroupID INNER JOIN
                         ManCoDocs ON GroupDataRights.ManCoDocID = ManCoDocs.ManCoDocID INNER JOIN
                         ManagementCompany ON ManCoDocs.ManCoID = ManagementCompany.ManCoID INNER JOIN
                         DocumentType ON ManCoDocs.DocumentTypeID = DocumentType.DocumentTypeID INNER JOIN
                         EnvironmentType ON ManCoDocs.EnvironmentID = EnvironmentType.EnvironmentID
where ManCoDocs.Active = 1
ORDER BY [Group].GroupName, ManagementCompany.ManCoCode, DocumentType.BravuraDocTypeCode, ManCoDocs.VersionComments
END


GO
/****** Object:  StoredProcedure [dbo].[nmBBOSSummaryUserGroupReport]    Script Date: 21/04/2015 11:34:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Asa Burrows>
-- Create date: <05/11/2014>
-- Description:	<BBOS Summary User Group Report Query>
-- =============================================
CREATE PROCEDURE [dbo].[nmBBOSSummaryUserGroupReport]
	-- No parameters
AS
BEGIN

SELECT DISTINCT AspNetUsers.UserName, AspNetUsers.FirstName, AspNetUsers.LastName, [Group].GroupName, AspNetUsers.Email, AspNetUsers.Phone
FROM            AspNetUsers INNER JOIN
                         UserGroups ON AspNetUsers.Id = UserGroups.UserId INNER JOIN
                         [Group] ON UserGroups.GroupID = [Group].GroupID
ORDER BY AspNetUsers.FirstName, AspNetUsers.LastName DESC, [Group].GroupName

END

GO
/****** Object:  StoredProcedure [dbo].[nmBBOSSummaryUserReport]    Script Date: 21/04/2015 11:34:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Asa Burrows>
-- Create date: <05/11/2014>
-- Description:	<BBOS Basic Summary User Report Query>
-- =============================================
CREATE PROCEDURE [dbo].[nmBBOSSummaryUserReport]
	-- No parameters
AS
BEGIN
SELECT 'DST Active Users' as [Description], 1 as [Order], UserName, FirstName, LastName, Title, Email, Comment, LastLoginDate, LastPasswordChangedDate, IsLockedOut, FailedLogInCount, Phone, IsApproved
FROM            AspNetUsers
WHERE        (Email LIKE N'%dstoutput%') AND (IsLockedOut = 0) AND (IsApproved = 1)
UNION

SELECT 'DST Active Users Locked Out' as [Description], 2 as [Order], UserName, FirstName, LastName, Title, Email, Comment, LastLoginDate, LastPasswordChangedDate, IsLockedOut, FailedLogInCount, Phone, IsApproved
FROM            AspNetUsers
WHERE        (Email LIKE N'%dstoutput%') AND (IsLockedOut = 1) AND (IsApproved = 1)
UNION

SELECT 'DST Inactive Users' as [Description], 3 as [Order], UserName, FirstName, LastName, Title, Email, Comment, LastLoginDate, LastPasswordChangedDate, IsLockedOut, FailedLogInCount, Phone, IsApproved
FROM            AspNetUsers
WHERE        (Email LIKE N'%dstoutput%') AND (IsApproved = 0)
UNION

SELECT 'Bravura Active Users' as [Description], 4 as [Order], UserName, FirstName, LastName, Title, Email, Comment, LastLoginDate, LastPasswordChangedDate, IsLockedOut, FailedLogInCount, Phone, IsApproved
FROM            AspNetUsers
WHERE        (Email LIKE N'%bravura%') AND (IsLockedOut = 0) AND (IsApproved = 1)
UNION

SELECT 'Bravura Active Users Locked Out' as [Description], 5 as [Order], UserName, FirstName, LastName, Title, Email, Comment, LastLoginDate, LastPasswordChangedDate, IsLockedOut, FailedLogInCount, Phone, IsApproved
FROM            AspNetUsers
WHERE        (Email LIKE N'%bravura%') AND (IsLockedOut = 1) AND (IsApproved = 1)
UNION

SELECT 'Bravura Inactive Users' as [Description], 6 as [Order], UserName, FirstName, LastName, Title, Email, Comment, LastLoginDate, LastPasswordChangedDate, IsLockedOut, FailedLogInCount, Phone, IsApproved
FROM            AspNetUsers
WHERE        (Email LIKE N'%bravura%') AND (IsApproved = 0)
UNION

SELECT 'BNYM Active Users' as [Description], 7 as [Order], UserName, FirstName, LastName, Title, Email, Comment, LastLoginDate, LastPasswordChangedDate, IsLockedOut, FailedLogInCount, Phone, IsApproved
FROM            AspNetUsers
WHERE        (Email LIKE N'%bnym%') AND (IsLockedOut = 0) AND (IsApproved = 1)
UNION

SELECT 'BNYM Active Users Locked Out' as [Description], 8 as [Order], UserName, FirstName, LastName, Title, Email, Comment, LastLoginDate, LastPasswordChangedDate, IsLockedOut, FailedLogInCount, Phone, IsApproved
FROM            AspNetUsers
WHERE        (Email LIKE N'%bnym%') AND (IsLockedOut = 1) AND (IsApproved = 1)
UNION

SELECT 'BNYM Inactive Users' as [Description], 9 as [Order], UserName, FirstName, LastName, Title, Email, Comment, LastLoginDate, LastPasswordChangedDate, IsLockedOut, FailedLogInCount, Phone, IsApproved
FROM            AspNetUsers
WHERE        (Email LIKE N'%bnym%') AND (IsApproved = 0)
ORDER BY [Order], FirstName, LastName

END

GO
/****** Object:  StoredProcedure [dbo].[nmCloneIndexDefinition]    Script Date: 21/04/2015 11:34:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Asa Burrows>
-- Create date: <05/06/2014>
-- Description:	<Clones an index definition, it does not close any child objects>
-- =============================================
CREATE PROCEDURE [dbo].[nmCloneIndexDefinition] 
	-- Add the parameters for the stored procedure here
	@IndexDefinitionID				int,
	@NewIndexDefinitionManCoDocID	int,
	@NewIndexDefinitionID			int out
AS
BEGIN

INSERT INTO IndexDefinition
 ( ManCoDocID
      ,IndexDefinitionName
      ,[ExstreamSymbolicIndexName]
      ,[ExstreamSymbolicDataName]
      ,[ExstreamSymbolicReportName]
      ,[ReportIsArchive]
      ,[LayoutFileID]
      ,[ReportLayoutFileID]
      ,[MailSortSettingID]
)
SELECT  @NewIndexDefinitionManCoDocID
      ,id.[IndexDefinitionName]
      ,id.[ExstreamSymbolicIndexName]
      ,id.[ExstreamSymbolicDataName]
      ,id.[ExstreamSymbolicReportName]
      ,id.[ReportIsArchive]
      ,id.[LayoutFileID]
      ,id.[ReportLayoutFileID]
      ,null
  FROM IndexDefinition id
  where id.IndexDefinitionID = @IndexDefinitionID  

  set @NewIndexDefinitionID = @@IDENTITY
END

GO
/****** Object:  StoredProcedure [dbo].[nmCreateJob]    Script Date: 21/04/2015 11:34:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[nmCreateJob]
	-- Add the parameters for the stored procedure here
	@ManCoDocID int,
    @GRID nvarchar(30),
	@AllocatorGRID nvarchar (30) -- This is the Allocator GRID from Nexdox App NMBBS01 used for support/debugging only.
    --@createDate datetime = GetDate,    
    --@SubmissionDate datetime = GetDate,
    --@AdditionalSetupInfo nvarchar(500),
    --@UserID nvarchar(128)    
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	INSERT INTO [Job]
           ([ManCoDocID]
           ,[GRID]
           ,[createDate]         
           ,[SubmissionDate]
           ,[AdditionalSetupInfo]
           ,[UserID]
		   ,[ManuallySubmitted]
		   ,[AllocatorGRID]
		   ,[JobStatusTypeID])
     VALUES
			(@ManCoDocID
			,@GRID
            ,GetDate()         
            ,GetDate()
            ,null
			,'e6f5eb69-2541-49f4-98a4-1329e8dd81c7' -- This is the OneStepProcess user. Has no portal access right just used for logging again job creations.
			,0
			,@AllocatorGRID
			,133)
	
	Declare @newJobID int
	set @newJobID = @@IDENTITY
	
	select @newJobID 
		
END
GO
/****** Object:  StoredProcedure [dbo].[nmGetAdditionalReportingForMediaDefinitions]    Script Date: 21/04/2015 11:34:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Asa Burrows>
-- Create date: <01/06/2014>
-- Description:	<Get Additonal reporting for Media Definitions>
-- =============================================
CREATE PROCEDURE [dbo].[nmGetAdditionalReportingForMediaDefinitions] 
	-- Add the parameters for the stored procedure here
	@MediaDefinitionID	int
AS
BEGIN

SELECT *
  FROM AdditionalReporting as ar
  left join LayoutFile lf on ar.ReportLayoutFileID = lf.LayoutFileID
  where ar.MediaDefinitionID = @MediaDefinitionID
  END

GO
/****** Object:  StoredProcedure [dbo].[nmGetArchiveLayoutFieldsForManCoDoc]    Script Date: 21/04/2015 11:34:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Asa Burrows>
-- Create date: <22/05/2014>
-- Description:	<Get ManCoDoc Index Definitions>
-- =============================================
CREATE PROCEDURE [dbo].[nmGetArchiveLayoutFieldsForManCoDoc] 
	-- Add the parameters for the stored procedure here
	@ManCoDocID	int
AS
BEGIN

SELECT	lf.*
		,null as 'LayoutFieldID'
		,laf.FieldName             
		,laf.ArchiveIndexFieldName 
		,laf.FieldType             
		,laf.Offset                
		,laf.[Length]                
		,laf.LayoutArchiveFieldID  
		,laf.ManCoDocID            
		,laf.FileNameOrder
		,laf.TrimFieldSeparator
		,null as 'FieldRole'

  FROM LayoutArchiveFields as laf
  inner join LayoutFile lf on lf.LayoutFileID = laf.LayoutFileID
  where laf.ManCoDocID = @ManCoDocID
  END

GO
/****** Object:  StoredProcedure [dbo].[nmGetArchiveLayoutFieldsForManCoDoc_v1.1]    Script Date: 21/04/2015 11:34:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Asa Burrows>
-- Create date: <22/05/2014>
-- Description:	<Get ManCoDoc Index Definitions>
-- =============================================
CREATE PROCEDURE [dbo].[nmGetArchiveLayoutFieldsForManCoDoc_v1.1] 
	-- Add the parameters for the stored procedure here
	@ManCoDocID	int
AS
BEGIN

SELECT	lf.*
		,null as 'LayoutFieldID'
		,laf.FieldName             
		,laf.ArchiveIndexFieldName 
		,laf.FieldType             
		,laf.Offset                
		,laf.[Length]                
		,laf.LayoutArchiveFieldID  
		,laf.ManCoDocID            
		,laf.FileNameOrder
		,laf.TrimFieldSeparator
		,null as 'FieldRole'

  FROM LayoutArchiveFields as laf
  inner join LayoutFile lf on lf.LayoutFileID = laf.LayoutFileID
  where laf.ManCoDocID = @ManCoDocID
  END

GO
/****** Object:  StoredProcedure [dbo].[nmGetHPExstreamControlFileSwitches]    Script Date: 21/04/2015 11:34:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Asa Burrows>
-- Create date: <12/06/2014>
-- Description:	<Get a list of special HP Exstream Control Switches>
-- =============================================
CREATE PROCEDURE [dbo].[nmGetHPExstreamControlFileSwitches] 
	-- Add the parameters for the stored procedure here
	@ManCoDocID	int
AS
BEGIN

SELECT *
  FROM HPExstreamControlFileSwitch as cfs
  where cfs.ManCoDocID = @ManCoDocID
  END

GO
/****** Object:  StoredProcedure [dbo].[nmGetHPExstreamReferenceFiles]    Script Date: 21/04/2015 11:34:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Asa Burrows>
-- Create date: <22/05/2014>
-- Description:	<Get ManCoDoc Index Definitions>
-- =============================================
CREATE PROCEDURE [dbo].[nmGetHPExstreamReferenceFiles] 
	-- Add the parameters for the stored procedure here
	@ManCoDocID	int
AS
BEGIN

SELECT *
  FROM HPExstreamReferenceFiles as rf
  where rf.ManCoDocID = @ManCoDocID
  END

GO
/****** Object:  StoredProcedure [dbo].[nmGetJob]    Script Date: 21/04/2015 11:34:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Asa Burrows>
-- Create date: <22/05/2014>
-- Description:	<Returns a ManCoDocID based on the input parameters. In Theory there should only ever be 1 result however if tere are more, throw erros in your code.>
-- =============================================
CREATE PROCEDURE [dbo].[nmGetJob] 
	-- Add the parameters for the stored procedure here
	@JobID			int
AS
BEGIN

SELECT *
  FROM [Job] as job
  inner join AspNetUsers usr on usr.Id = job.UserID
  where job.JobID = @JobID  
END

GO
/****** Object:  StoredProcedure [dbo].[nmGetLayoutFileFields]    Script Date: 21/04/2015 11:34:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Asa Burrows>
-- Create date: <04/06/2014>
-- Description:	<Get ManCoDoc Index Definitions>
-- =============================================
CREATE PROCEDURE [dbo].[nmGetLayoutFileFields] 
	-- Add the parameters for the stored procedure here
	@LayoutFileID	int
AS
BEGIN

DECLARE @TrimFieldSeparator bit
set @TrimFieldSeparator = 0

SELECT lf.*
		,laf.LayoutFieldID
		,laf.FieldName             
		,null as 'ArchiveIndexFieldName'
		,laf.FieldType             
		,laf.Offset                
		,laf.[Length]                
		,null  as 'LayoutArchiveFieldID'
		,null  as 'ManCoDocID'        
		,null  as 'FileNameOrder'
		,@TrimFieldSeparator as 'TrimFieldSeparator'
		,laf.FieldRole

  FROM LayoutFileFields as laf
  inner join LayoutFile lf on lf.LayoutFileID = laf.LayoutFileID
  where laf.LayoutFileID = @LayoutFileID
  END

GO
/****** Object:  StoredProcedure [dbo].[nmGetLayoutFileFields_v1.0]    Script Date: 21/04/2015 11:34:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Asa Burrows>
-- Create date: <04/06/2014>
-- Description:	<Get ManCoDoc Index Definitions>
-- =============================================
CREATE PROCEDURE [dbo].[nmGetLayoutFileFields_v1.0]
	-- Add the parameters for the stored procedure here
	@LayoutFileID	int
AS
BEGIN

DECLARE @TrimFieldSeparator bit
set @TrimFieldSeparator = 0

SELECT lf.*
		,laf.LayoutFieldID
		,laf.FieldName             
		,null as 'ArchiveIndexFieldName'
		,laf.FieldType             
		,laf.Offset                
		,laf.[Length]                
		,null  as 'LayoutArchiveFieldID'
		,null  as 'ManCoDocID'        
		,null  as 'FileNameOrder'
		,@TrimFieldSeparator as 'TrimFieldSeparator'

  FROM LayoutFileFields as laf
  inner join LayoutFile lf on lf.LayoutFileID = laf.LayoutFileID
  where laf.LayoutFileID = @LayoutFileID
  END

GO
/****** Object:  StoredProcedure [dbo].[nmGetLayoutFileFields_v1.1]    Script Date: 21/04/2015 11:34:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Asa Burrows>
-- Create date: <04/06/2014>
-- Description:	<Get ManCoDoc Index Definitions>
-- =============================================
CREATE PROCEDURE [dbo].[nmGetLayoutFileFields_v1.1] 
	-- Add the parameters for the stored procedure here
	@LayoutFileID	int
AS
BEGIN

DECLARE @TrimFieldSeparator bit
set @TrimFieldSeparator = 0

SELECT lf.*
		,laf.LayoutFieldID
		,laf.FieldName             
		,null as 'ArchiveIndexFieldName'
		,laf.FieldType             
		,laf.Offset                
		,laf.[Length]                
		,null  as 'LayoutArchiveFieldID'
		,null  as 'ManCoDocID'        
		,null  as 'FileNameOrder'
		,@TrimFieldSeparator as 'TrimFieldSeparator'
		,laf.FieldRole

  FROM LayoutFileFields as laf
  inner join LayoutFile lf on lf.LayoutFileID = laf.LayoutFileID
  where laf.LayoutFileID = @LayoutFileID
  END

GO
/****** Object:  StoredProcedure [dbo].[nmGetManCoDocIndexDefinitions]    Script Date: 21/04/2015 11:34:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Asa Burrows>
-- Create date: <22/05/2014>
-- Description:	<Get ManCoDoc Index Definitions>
-- =============================================
CREATE PROCEDURE [dbo].[nmGetManCoDocIndexDefinitions] 
	-- Add the parameters for the stored procedure here
	@ManCoDocID	int
AS
BEGIN

SELECT *
  FROM [IndexDefinition] as id
  where id.ManCoDocID = @ManCoDocID
  END

GO
/****** Object:  StoredProcedure [dbo].[nmGetManCoDocs]    Script Date: 21/04/2015 11:34:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Asa Burrows>
-- Create date: <22/05/2014>
-- Description:	<Returns a ManCoDocID based on the input parameters. In Theory there should only ever be 1 result however if tere are more, throw erros in your code.>
-- =============================================
CREATE PROCEDURE [dbo].[nmGetManCoDocs] 
	-- Add the parameters for the stored procedure here
	@ManCoCode			char(2),
	@DocumentTypeCode	char(3),
	@AgentDocument		bit,
	@Environment		int
AS
BEGIN

SELECT *
  FROM [ManCoDocs] as mcd
  inner join ManagementCompany mc on mc.ManCoID = mcd.ManCoID
  inner join DocumentType dt on dt.DocumentTypeID = mcd.DocumentTypeID
  inner join EnvironmentType et on mcd.EnvironmentID = et.EnvironmentID
  inner join ParentCompany pc on mc.ParentCompanyID = pc.ParentCompanyID
  where mc.ManCoCode = @ManCoCode 
	and dt.BravuraDocTypeCode = @DocumentTypeCode 
	and dt.AgentDocument = @AgentDocument 
	and mcd.Active = 1
	and @Environment = mcd.EnvironmentID
  
END

GO
/****** Object:  StoredProcedure [dbo].[nmGetManCoDocWithID]    Script Date: 21/04/2015 11:34:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Asa Burrows>
-- Create date: <22/05/2014>
-- Description:	<Returns a ManCoDocID based on the input parameters. In Theory there should only ever be 1 result however if tere are more, throw erros in your code.>
-- =============================================
CREATE PROCEDURE [dbo].[nmGetManCoDocWithID] 
	-- Add the parameters for the stored procedure here
	@ManCoDocID			int
AS
BEGIN

SELECT *
  FROM [ManCoDocs] as mcd
  inner join ManagementCompany mc on mc.ManCoID = mcd.ManCoID
  inner join DocumentType dt on dt.DocumentTypeID = mcd.DocumentTypeID
  inner join EnvironmentType et on mcd.EnvironmentID = et.EnvironmentID
  inner join ParentCompany pc on mc.ParentCompanyID = pc.ParentCompanyID
  where mcd.ManCoDocID = @ManCoDocID  
END

GO
/****** Object:  StoredProcedure [dbo].[nmGetMediaDefinitionsForIndexDefinition]    Script Date: 21/04/2015 11:34:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- ======================================================================
-- Author:		<Asa Burrows>
-- Create date: <22/05/2014>
-- Description:	<Get Media Definitions Associated to an Index Definition>
-- ======================================================================
CREATE PROCEDURE [dbo].[nmGetMediaDefinitionsForIndexDefinition] 
	-- Add the parameters for the stored procedure here
	@IndexDefinitionID	int
AS
BEGIN

SELECT *
  FROM [MediaDefinition] as md
  where md.IndexDefinitionID = @IndexDefinitionID
  END
GO
/****** Object:  StoredProcedure [dbo].[nmGetMediaStocks]    Script Date: 21/04/2015 11:34:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Asa Burrows>
-- Create date: <30/07/2014>
-- Description:	<Returns a list of Media Stocks madeup from the MediaStock and Stock table.>
-- =============================================
CREATE PROCEDURE [dbo].[nmGetMediaStocks] 
	-- Add the parameters for the stored procedure here
	@MediaDefID			int
AS
BEGIN

SELECT *
  FROM MediaStock as ms
  inner join Stock s on ms.StockID = s.StockID
  inner join StockType st on s.StockTypeID = st.StockTypeID
  where ms.MediaDefID = @MediaDefID
  
END

GO
/****** Object:  StoredProcedure [dbo].[nmGetSortFields]    Script Date: 21/04/2015 11:34:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Asa Burrows>
-- Create date: <05/06/2014>
-- Description:	<Get Sort Fields for Index Definition>
-- =============================================
CREATE PROCEDURE [dbo].[nmGetSortFields] 
	-- Add the parameters for the stored procedure here
	@IndexDefinitionID	int
AS
BEGIN

SELECT *
  FROM Sort as s
  inner join DirectionType dt on s.DirectionTypeID = dt.DirectionTypeID
  where s.IndexDefinitionID = @IndexDefinitionID
  END

GO
/****** Object:  StoredProcedure [dbo].[nmInsertEnclosingFile]    Script Date: 21/04/2015 11:34:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Asa Burrows>
-- Create date: <04/09/2014>
-- Description:	<Adds a print file entry to the Enclosing Table.>
-- =============================================
CREATE PROCEDURE [dbo].[nmInsertEnclosingFile]
	-- Add the parameters for the stored procedure here
	@JobID int,
	@MediaDefID int,
	@FBEID int,
	@Packs int,
	@Pages int,
	@Sheets int,
	@Filename nvarchar(250),
	@DigiQGRID nvarchar(30)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	-- We need to delete the original records for this print file first and then re insert
	Declare @EnclosingJobID int
	set @EnclosingJobID = null
	select top 1 @EnclosingJobID = EnclosingJobID
	from EnclosingJob
	where JobID = @JobID and MediaDefID = @MediaDefID and Filename = @Filename

	if (@EnclosingJobID is not null)
	begin
		delete from MaterialConsumption
		where EnclosingID = @EnclosingJobID
	end 

	delete from EnclosingJob
	where JobID = @JobID and MediaDefID = @MediaDefID and Filename = @Filename

    -- Insert statements for procedure here
	INSERT INTO [EnclosingJob]
           ([FBE_ID]
           ,[JobID]
           ,[MediaDefID]         
           ,[Packs]
           ,[Pages]
           ,[Sheets]
		   ,[Filename]
		   ,[DigiQGRID])
     VALUES
			(@FBEID
			,@JobID
            ,@MediaDefID         
            ,@Packs
            ,@Pages
			,@Sheets
			,@Filename
			,@DigiQGRID)
	
	Declare @newEnclosingJobID int
	set @newEnclosingJobID = @@IDENTITY
	select @newEnclosingJobID
END
GO
/****** Object:  StoredProcedure [dbo].[nmInsertInputFileIntoBBOSDB]    Script Date: 21/04/2015 11:34:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Asa Burrows>
-- Create date: <17/06/2014>
-- Description:	<Insert infput file record into BBOS Database>
-- =============================================
CREATE PROCEDURE [dbo].[nmInsertInputFileIntoBBOSDB]
	-- Add the parameters for the stored procedure here
	@FileName nvarchar(250),
	@Path nvarchar(250),
	@JobID int,
	@ManCoDocID int,
	@FileTypeID int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	INSERT INTO InputFile
           ([Filename]
           ,FilePath
           ,ReceivedDate        
           ,JobID
           ,ManCoDocID
           ,FileTypeID)
SELECT  @FileName, @Path, GetDate(), @JobID, @ManCoDocID, @FileTypeID
WHERE   NOT EXISTS
        (
        SELECT  [FileName], JobID
        FROM   InputFile inf
		where [FileName] = @FileName and inf.JobID = @JobID
        )		
END
GO
/****** Object:  StoredProcedure [dbo].[nmInsertMaterialConsumption]    Script Date: 21/04/2015 11:34:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Asa Burrows>
-- Create date: <04/09/2014>
-- Description:	<Adds a material consumption information of a print file into the MaterialConsumption table.>
-- =============================================
CREATE PROCEDURE [dbo].[nmInsertMaterialConsumption]
	-- Add the parameters for the stored procedure here
	@EnclosingID int,
	@MediaStockID int,
	@Quantity int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	INSERT INTO [MaterialConsumption]
           ([EnclosingID]
           ,[MediaStockId]
           ,[Qty])
     VALUES
			(@EnclosingID
			,@MediaStockID
            ,@Quantity)
	
	Declare @newMaterialConsumptionID int
	set @newMaterialConsumptionID = @@IDENTITY
	select @newMaterialConsumptionID
END

GO
/****** Object:  StoredProcedure [dbo].[nmIsJobHeld]    Script Date: 21/04/2015 11:34:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Asa Burrows>
-- Create date: <19/11/2014>
-- Description:	<Checks all the Mediadefs for a job and if they are all set to hold then this method returns 1 (true)>
-- =============================================
CREATE PROCEDURE [dbo].[nmIsJobHeld] 
	-- Add the parameters for the stored procedure here
	@GRID			nvarchar(30)
AS
BEGIN

DECLARE @Hold bit

SELECT distinct Hold
  FROM job
  inner join MediaDefinition on MediaDefinition.ManCoDocID = Job.ManCoDocID
  INNER JOIN EnclosingJob ON Job.JobID = EnclosingJob.JobID AND MediaDefinition.MediaDefID = EnclosingJob.MediaDefID

  where job.GRID = @GRID
  
  if @@ROWCOUNT = 1
    BEGIN
	  SELECT @Hold = Hold
	    FROM Job 
		  inner join MediaDefinition on MediaDefinition.ManCoDocID = Job.ManCoDocID
		  INNER JOIN EnclosingJob ON Job.JobID = EnclosingJob.JobID AND MediaDefinition.MediaDefID = EnclosingJob.MediaDefID
	    where job.GRID = @GRID

	  return @Hold
	END
  ELSE
    BEGIN
	  return 0
    END
END

GO
/****** Object:  StoredProcedure [dbo].[nmSelectEnclosingFileItems]    Script Date: 21/04/2015 11:34:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Asa Burrows>
-- Create date: <18/11/2014>
-- Description:	<This procedure returns a list of Enclosing File Records for a specific JobID>
-- =============================================
CREATE PROCEDURE [dbo].[nmSelectEnclosingFileItems]
	-- Add the parameters for the stored procedure here
	@JobID			int
AS
BEGIN

SELECT  EnclosingJob.[EnclosingJobID]
      ,EnclosingJob.[FBE_ID]
      ,EnclosingJob.[JobID]
      ,EnclosingJob.[MediaDefID]
      ,EnclosingJob.[Packs]
      ,EnclosingJob.[Pages]
      ,EnclosingJob.[Sheets]
      ,EnclosingJob.[Filename]
      ,EnclosingJob.[DigiQGRID]
      ,EnclosingJob.[PostalDocketNumber]
	  ,MediaDefinition.Colour
	  ,MediaDefinition.QueueName
	  
FROM	EnclosingJob INNER JOIN
		MediaDefinition ON EnclosingJob.MediaDefID = MediaDefinition.MediaDefID 
WHERE			EnclosingJob.JobID = @JobID

END

GO
/****** Object:  StoredProcedure [dbo].[nmSelectEnclosingFilewithFBE]    Script Date: 21/04/2015 11:34:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- Batch submitted through debugger: dbo.nmSelectEnclosingFilewithFBE.sql|2|0|C:\Nexdox\nmbbs02\c#\nmbbs02\SQL\dbo.nmSelectEnclosingFilewithFBE.sql
-- =============================================
-- Author:		<Asa Burrows>
-- Create date: <31/08/2014>
-- Description:	<Returns the GRIB for a specific FBE from the Enclosing Table.>
-- =============================================
CREATE PROCEDURE [dbo].[nmSelectEnclosingFilewithFBE] 
	-- Add the parameters for the stored procedure here
	@PitneyNumber		int
AS
BEGIN

SELECT *
  FROM EnclosingJob as enc
  where enc.FBE_ID = @PitneyNumber 
END

GO
/****** Object:  StoredProcedure [dbo].[nmSelectManCoJobsForAMonth]    Script Date: 21/04/2015 11:34:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Asa Burrows>
-- Create date: <30/09/2014>
-- Description:	<This procedure returns a list of jobs processed in a specific Month for a specific Manco.>
-- =============================================
CREATE PROCEDURE [dbo].[nmSelectManCoJobsForAMonth]
	-- Add the parameters for the stored procedure here
	@Month			int,
	@ManCoCode		char (2) = null,
	@DocTypeCode	char (3) = null,
	@Environment	int
AS
BEGIN

SELECT        Job.JobID, Job.GRID, Job.createDate as CreateDate, ManagementCompany.ManCoCode, ManagementCompany.ManCoName, ManagementCompany.ManCoShortName, DocumentType.BravuraDocTypeCode, Job.JobStatusTypeID, 
                         JobStatusType.JobStatusDescription, ManCoDocs.EnvironmentID, ManCoDocs.ManualSubmissionOnly, DocumentType.AgentDocument, EnvironmentType.ProcessingServerName, EnvironmentType.BravuraDOCSEnvironmentChar
FROM            Job INNER JOIN
                         ManCoDocs ON Job.ManCoDocID = ManCoDocs.ManCoDocID INNER JOIN
                         ManagementCompany ON ManCoDocs.ManCoID = ManagementCompany.ManCoID INNER JOIN
                         DocumentType ON ManCoDocs.DocumentTypeID = DocumentType.DocumentTypeID INNER JOIN
                         JobStatusType ON Job.JobStatusTypeID = JobStatusType.JobStatusTypeID INNER JOIN
						 EnvironmentType ON ManCoDocs.EnvironmentID = EnvironmentType.EnvironmentID
WHERE			(Job.JobStatusTypeID IN (1,54,139)) AND (DATEPART(month, Job.createDate) = @Month) AND 
				(@ManCoCode is null or ManagementCompany.ManCoCode = @ManCoCode) AND 
				(@DocTypeCode is null or DocumentType.BravuraDocTypeCode = @DocTypeCode) AND 
				(ManCoDocs.EnvironmentID = @Environment)
  END

GO
/****** Object:  StoredProcedure [dbo].[nmSelectManCoJobsForAMonth_v1.0]    Script Date: 21/04/2015 11:34:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Asa Burrows>
-- Create date: <30/09/2014>
-- Description:	<This procedure returns a list of jobs processed in a specific Month for a specific Manco.>
-- =============================================
CREATE PROCEDURE [dbo].[nmSelectManCoJobsForAMonth_v1.0]
	-- Add the parameters for the stored procedure here
	@Month			int,
	@ManCoCode		char (2) = null,
	@DocTypeCode	char (3) = null,
	@Environment	int
AS
BEGIN

SELECT        Job.JobID, Job.GRID, Job.createDate as CreateDate, ManagementCompany.ManCoCode, ManagementCompany.ManCoName, DocumentType.BravuraDocTypeCode, Job.JobStatusTypeID, 
                         JobStatusType.JobStatusDescription, ManCoDocs.EnvironmentID, DocumentType.AgentDocument, EnvironmentType.ProcessingServerName, EnvironmentType.BravuraDOCSEnvironmentChar
FROM            Job INNER JOIN
                         ManCoDocs ON Job.ManCoDocID = ManCoDocs.ManCoDocID INNER JOIN
                         ManagementCompany ON ManCoDocs.ManCoID = ManagementCompany.ManCoID INNER JOIN
                         DocumentType ON ManCoDocs.DocumentTypeID = DocumentType.DocumentTypeID INNER JOIN
                         JobStatusType ON Job.JobStatusTypeID = JobStatusType.JobStatusTypeID INNER JOIN
						 EnvironmentType ON ManCoDocs.EnvironmentID = EnvironmentType.EnvironmentID
WHERE			(Job.JobStatusTypeID = 139) AND (DATEPART(month, Job.createDate) = @Month) AND 
				(@ManCoCode is null or ManagementCompany.ManCoCode = @ManCoCode) AND 
				(@DocTypeCode is null or DocumentType.BravuraDocTypeCode = @DocTypeCode) AND 
				(ManCoDocs.EnvironmentID = @Environment)
  END

GO
/****** Object:  StoredProcedure [dbo].[nmSelectMaterialConsumptionItems]    Script Date: 21/04/2015 11:34:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Asa Burrows>
-- Create date: <18/11/2014>
-- Description:	<This procedure returns a list of Enclosing File Records for a specific JobID>
-- =============================================
CREATE PROCEDURE [dbo].[nmSelectMaterialConsumptionItems]
	-- Add the parameters for the stored procedure here
	@EnclosingJobID			int
AS
BEGIN

SELECT  MaterialConsumption.MaterialConsumptionID
		,MaterialConsumption.EnclosingID
		,MaterialConsumption.MediaStockId
		,MaterialConsumption.Qty
		,Stock.StockTypeID
FROM	MaterialConsumption
INNER JOIN MediaStock on MediaStock.MediaStockID = MaterialConsumption.MediaStockId
INNER JOIN Stock on Stock.StockID = MediaStock.StockID
WHERE	MaterialConsumption.EnclosingID = @EnclosingJobID

END

GO
/****** Object:  StoredProcedure [dbo].[nmSELMailsortSetting]    Script Date: 21/04/2015 11:34:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Tommy Rushton>
-- Create date: <08/09/2014>
-- Description:	<Get Mailsort Setting definition>
-- =============================================
CREATE PROCEDURE [dbo].[nmSELMailsortSetting] 
	-- Add the parameters for the stored procedure here
	@MailsortSettingID	int
AS
BEGIN

SELECT *
  FROM [MailsortSetting] as id
  where id.MailsortSettingID = @MailsortSettingID
  END

GO
/****** Object:  StoredProcedure [dbo].[nmUpdateJobGRIDs]    Script Date: 21/04/2015 11:34:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Asa Burrows>
-- Create date: <22/05/2014>
-- Description:	<Update Job Information>
-- =============================================
CREATE PROCEDURE [dbo].[nmUpdateJobGRIDs]
	-- Add the parameters for the stored procedure here
	@JobID int,
    @GRID nvarchar(30),
	@AllocatorGRID nvarchar (30) -- This is the Allocator GRID used for support/debugging only.
AS
begin
    -- Update
	UPDATE [Job]
   SET [GRID] = @GRID
      ,[AllocatorGRID] = @AllocatorGRID
 WHERE JobID = @JobID
 end

GO
/****** Object:  StoredProcedure [dbo].[nmUpdateJobStatus]    Script Date: 21/04/2015 11:34:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Asa Burrows>
-- Create date: <10/06/2014>
-- Description:	<Update Job Status>
-- =============================================
CREATE PROCEDURE [dbo].[nmUpdateJobStatus]
	-- Add the parameters for the stored procedure here
	@JobID int,
    @JobStatusTypeID int
AS
begin
    -- Update
	UPDATE [Job]
   SET JobStatusTypeID = @JobStatusTypeID
 WHERE JobID = @JobID
 end

GO
/****** Object:  StoredProcedure [dbo].[PromoteMancoDocs]    Script Date: 21/04/2015 11:34:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[PromoteMancoDocs]
	-- Add the parameters for the stored procedure here
	@ManCoDocIDs varchar(max),
	@SourceAppEnvironment varchar(50),  
	@TargetAppEnvironment varchar(50),    
    @UserID nvarchar(128),	
	@Comment nvarchar(500)=NULL    
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	BEGIN TRANSACTION [PromoteMancoDoc]		
	
	declare @MancoDocIdsTable table(sourceMancoDocId int)	
	
	declare @SourceAppEnvironmentID int;
	declare @TargetAppEnvironmentID int;
	
	declare @sourceEnvironmentTypeId int;
	declare @targetEnvironmentTypeId int;
	
	
	select top 1 @sourceEnvironmentTypeId=EnvironmentID,@SourceAppEnvironmentID=ae.AppEnvironmentID from EnvironmentType et
	inner join AppEnvironment ae on ae.AppEnvironmentID=et.AppEnvironmentID
	where ae.Name=@SourceAppEnvironment
	
	select top 1 @targetEnvironmentTypeId=EnvironmentID,@TargetAppEnvironmentID=ae.AppEnvironmentID  from EnvironmentType et
	inner join AppEnvironment ae on ae.AppEnvironmentID=et.AppEnvironmentID
	 where ae.Name=@TargetAppEnvironment
	
	Insert into @MancoDocIdsTable
    select SplitValue from [dbo].[Split](@ManCoDocIDs,',')
    
	While((select COUNT(*) from @MancoDocIdsTable )>0)
	Begin
	declare @sourceMancoDocId int;
	declare @targetMancoDocId int;
	declare @sourceMancoDocURI uniqueidentifier;
	
	set @sourceMancoDocId=(select top 1 sourceMancoDocId from @MancoDocIdsTable)
	set @sourceMancoDocURI=(select ManCoDocURI from ManCoDocs where ManCoDocID=@sourceMancoDocId)
	set @targetMancoDocId=(select ManCoDocID from ManCoDocs 
							where ManCoDocURI=@sourceMancoDocURI 
							and EnvironmentID=@targetEnvironmentTypeId and Active=1)
	--Insert/Update MancoDoc with new EnvironmentID
	if(@targetMancoDocId>0)
		Begin
			update dbo.ManCoDocs set Active=0 where ManCoDocID=@targetMancoDocId
		End		
	
		DECLARE @newMancoDocId int
		DECLARE @tempMancoDocTable table (Id int)
		
		insert into ManCoDocs ([BravuraDocID],[ManCoID],[DocumentTypeID],[PubFileName],[EnvironmentID]
		,[Version],[VersionComments],[Active],[Authorise],[ManualSubmissionOnly],[DateFolderCreation],[ManCoDocURI])
		output inserted.ManCoDocID into @tempMancoDocTable
		select [BravuraDocID],[ManCoID],[DocumentTypeID],[PubFileName],@targetEnvironmentTypeId
		,[Version],[VersionComments],[Active],[Authorise],[ManualSubmissionOnly],[DateFolderCreation],[ManCoDocURI]
		 from ManCoDocs where ManCoDocID=@sourceMancoDocId		
		
		select @newMancoDocId=Id from @tempMancoDocTable
		
		--Insert GroupDataRights			
		Insert into dbo.GroupDataRights ([GroupID],[ManCoDocID],[EnvironmentID])
		select [GroupID],@newMancoDocId,@TargetAppEnvironmentID 
		from  dbo.GroupDataRights where ManCoDocID=@sourceMancoDocId
		
		--Insert LayoutArchiveFields
		Insert into dbo.LayoutArchiveFields ([LayoutFileID],[LayoutFileType],[ManCoDocID],[FieldName],[ArchiveIndexFieldName]
		,[FieldType],[Offset],[Length],[FileNameOrder],[TrimFieldSeparator])
		select [LayoutFileID],[LayoutFileType],@newMancoDocId,[FieldName],[ArchiveIndexFieldName]
		,[FieldType],[Offset],[Length],[FileNameOrder],[TrimFieldSeparator]
		from  dbo.LayoutArchiveFields where ManCoDocID=@sourceMancoDocId
		
		--Insert HPExstreamReferenceFiles
		Insert into dbo.HPExstreamReferenceFiles ([ManCoDocID],[HPExstreamSymbolicName],[ReferenceFilePath],[MediaReferenceFile])
		select @newMancoDocId,[HPExstreamSymbolicName],[ReferenceFilePath],[MediaReferenceFile]
		from dbo.HPExstreamReferenceFiles where ManCoDocID=@sourceMancoDocId
		
		--Insert HPExstreamControlFileSwitch
		Insert into dbo.HPExstreamControlFileSwitch ([ManCoDocID],[ControlFileSwitch])
		select @newMancoDocId,[ControlFileSwitch] 
		from dbo.HPExstreamControlFileSwitch where ManCoDocID=@sourceMancoDocId
		
		--Insert IndexDefinitions
		DECLARE @newIndexDefId int
		DECLARE @newIndexDefinitionTable table (Id int)
		
		DECLARE @indexDefId int
		DECLARE @tempIndexDefinitionTable table (Id int)
		
		insert into @tempIndexDefinitionTable
		Select IndexDefinitionID from dbo.IndexDefinition where ManCoDocID=@sourceMancoDocId
		
		While((select COUNT(*) from @tempIndexDefinitionTable )>0)
		Begin			
			select top 1 @indexDefId=Id from @tempIndexDefinitionTable
			--Insert IndexDefinition
			insert into dbo.IndexDefinition ([ManCoDocID],[IndexDefinitionName],[ExstreamSymbolicIndexName],[ExstreamSymbolicDataName],[ExstreamSymbolicReportName]
			,[IndexDescription],[ReportIsArchive],[LayoutFileID] ,[ReportLayoutFileID],[MailSortSettingID])
			output inserted.IndexDefinitionID into @newIndexDefinitionTable
			select @newMancoDocId,[IndexDefinitionName],[ExstreamSymbolicIndexName],[ExstreamSymbolicDataName],[ExstreamSymbolicReportName]
			,[IndexDescription],[ReportIsArchive],[LayoutFileID] ,[ReportLayoutFileID],[MailSortSettingID]
			 from dbo.IndexDefinition where IndexDefinitionID=@indexDefId
			
			select @newIndexDefId=Id from @newIndexDefinitionTable
			
			--Insert Sort rows
			Insert into dbo.Sort ([SortField],[DirectionTypeID],[SortOrder],[IndexDefinitionID])
			select [SortField],[DirectionTypeID],[SortOrder],@newIndexDefId 
			from dbo.Sort where IndexDefinitionID=@indexDefId
			
			--Get MediaDefinitions and Insert new MediaDefinitions for each IndexDefinition
			DECLARE @newMediaDefId int
			DECLARE @newMediaDefinitionTable table (Id int)
			
			DECLARE @mediaDefId int
			DECLARE @tempMediaDefinitionTable table (Id int)
			
			insert into @tempMediaDefinitionTable
			Select MediaDefID from dbo.MediaDefinition where IndexDefinitionID=@indexDefId
			
			While((select COUNT(*) from @tempMediaDefinitionTable )>0)
			Begin
				select Top 1 @mediaDefId=Id from @tempMediaDefinitionTable
				--Insert MediaDefinition
				Insert Into dbo.MediaDefinition ([QueueName],[QueueNameSymbollic],[ManCoDocID],[IndexDefinitionID],[QueueDescription],[Hold]
				,[BatchSizeSheets],[MailsortSettingID],[FinishingDefID],[SortID],[Print],[PDF_Only],[createDate],[createUserID],[OutputFilename]
				,[OldQueueName],[ReportHPExstreamSymbolicName],[ReportLayoutFileID],[ReportIsArchive],[ReturnReportInDJA],[ReturnOutput]
				,[ShowOnShortReport],[ShowPDFOnWebPortal],[ShowReportOnWebPortal],[IsCD],[Colour],[EncloseEnvelope],[Action])
				output inserted.MediaDefID into @newMediaDefinitionTable
				select [QueueName],[QueueNameSymbollic],@newMancoDocId,@newIndexDefId,[QueueDescription],[Hold]
				,[BatchSizeSheets],[MailsortSettingID],[FinishingDefID],[SortID],[Print],[PDF_Only],GETDATE(),@UserID,[OutputFilename]
				,[OldQueueName],[ReportHPExstreamSymbolicName],[ReportLayoutFileID],[ReportIsArchive],[ReturnReportInDJA],[ReturnOutput]
				,[ShowOnShortReport],[ShowPDFOnWebPortal],[ShowReportOnWebPortal],[IsCD],[Colour],[EncloseEnvelope],[Action]
				from dbo.MediaDefinition where MediaDefID=@mediaDefId
				
				select @newMediaDefId=Id from @newMediaDefinitionTable
				
				--Insert MediaStock
				Insert into dbo.MediaStock ([StockID],[MediaDefID],[PublishDate],[StationNumber])
				select [StockID],@newMediaDefId,[PublishDate],[StationNumber] 
				from dbo.MediaStock where MediaDefID=@mediaDefId
				
				--Insert AdditionalReporting
				Insert into dbo.AdditionalReporting ([HPExstreamSymbolicName],[ReportLayoutFileID],[ReportDescription]
				,[MediaDefinitionID],[OutputFileName],[ReturnReportInDJA],[ShowReportOnWebPortal],[ManCoDocID])
				select [HPExstreamSymbolicName],[ReportLayoutFileID],[ReportDescription]
				,@newMediaDefId,[OutputFileName],[ReturnReportInDJA],[ShowReportOnWebPortal],@newMancoDocId
				from dbo.AdditionalReporting where MediaDefinitionID=@mediaDefId and ManCoDocID=@sourceMancoDocId
				
				delete from @tempMediaDefinitionTable where Id=@mediaDefId
				
			End				
			delete from @tempIndexDefinitionTable where Id=@indexDefId
		End	
		
		Insert into DeploymentHistory ([ManCoDocID],[PromoteDate],[Label],[UserID],[Comment])
		values(@sourceMancoDocId,GETDATE(),'Pre-BBOS',@UserID,@Comment)
		
		delete from @MancoDocIdsTable where sourceMancoDocId=@sourceMancoDocId
		End
		COMMIT TRANSACTION [PromoteMancoDoc]
END


GO
/****** Object:  StoredProcedure [dbo].[UpdateEnclosingJobDocketNumber]    Script Date: 21/04/2015 11:34:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE Procedure [dbo].[UpdateEnclosingJobDocketNumber]

@enclosingJobID int,
@postalDocketNumber nvarchar(30)=NULL

as
Begin
	update [dbo].[EnclosingJob]
	set PostalDocketNumber=@postalDocketNumber
	where EnclosingJobID=@enclosingJobID	
end
GO
/****** Object:  StoredProcedure [dbo].[UpdateJobStatus_Deleted]    Script Date: 21/04/2015 11:34:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[UpdateJobStatus_Deleted]

@JobId int,
@JobStatusTypeId int

as

Begin

Update JobStatus set JobStatusTypeID = @JobStatusTypeId, [DateTime] = GETDATE() where JobID = @JobId

End
GO
/****** Object:  StoredProcedure [dbo].[UpdateJobStatusType]    Script Date: 21/04/2015 11:34:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create procedure [dbo].[UpdateJobStatusType]

@JobId int,
@JobStatusTypeId int

as

Begin

Update Job set JobStatusTypeID = @JobStatusTypeId where JobID = @JobId

End
GO
/****** Object:  StoredProcedure [dbo].[UpdatePrintFlagForMediaDefinition]    Script Date: 21/04/2015 11:34:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[UpdatePrintFlagForMediaDefinition] 
	@MediaDefinitionId INT,
	@Print BIT
AS

UPDATE MediaDefinition SET [Print] = @Print WHERE MediaDefId = @MediaDefinitionId
GO
/****** Object:  StoredProcedure [dbo].[UpdateStationNumber]    Script Date: 21/04/2015 11:34:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[UpdateStationNumber] 
	@MediaStockId INT,
	@StationNumber INT
AS

UPDATE MediaStock SET StationNumber = @StationNumber WHERE MediaStockId = @MediaStockId
GO
/****** Object:  UserDefinedFunction [dbo].[Split]    Script Date: 21/04/2015 11:34:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE FUNCTION [dbo].[Split]
(
@String VARCHAR(max),
@Delimiter VARCHAR(5)
)
 
RETURNS @SplittedValues TABLE
(
OccurenceId SMALLINT IDENTITY(1,1),
SplitValue VARCHAR(200)
)
 
AS
 
BEGIN
 
DECLARE @SplitLength INT
 
WHILE LEN(@String) > 0
BEGIN
 
SELECT @SplitLength = (CASE CHARINDEX(@Delimiter,@String) WHEN 0 THEN
 
LEN(@String) ELSE CHARINDEX(@Delimiter,@String) -1 END)
 
INSERT INTO @SplittedValues
 
SELECT SUBSTRING(@String,1,@SplitLength)
 
SELECT @String = (CASE (LEN(@String) - @SplitLength) WHEN 0 THEN ''
 
ELSE RIGHT(@String, LEN(@String) - @SplitLength - 1) END)
 
END
RETURN
END

GO
/****** Object:  Table [dbo].[__MigrationHistory]    Script Date: 21/04/2015 11:34:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[__MigrationHistory](
	[MigrationId] [nvarchar](150) NOT NULL,
	[ContextKey] [nvarchar](300) NOT NULL,
	[Model] [varbinary](max) NOT NULL,
	[ProductVersion] [nvarchar](32) NOT NULL,
 CONSTRAINT [PK_dbo.__MigrationHistory] PRIMARY KEY CLUSTERED 
(
	[MigrationId] ASC,
	[ContextKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[AdditionalReporting]    Script Date: 21/04/2015 11:34:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[AdditionalReporting](
	[AdditionalReportID] [int] IDENTITY(1,1) NOT NULL,
	[HPExstreamSymbolicName] [varchar](50) NULL,
	[ReportLayoutFileID] [int] NULL,
	[ReportDescription] [varchar](200) NULL,
	[MediaDefinitionID] [int] NULL,
	[OutputFileName] [varchar](250) NOT NULL,
	[ReturnReportInDJA] [bit] NOT NULL CONSTRAINT [DF_AdditionalReporting_ReturnReportInDJA]  DEFAULT ((0)),
	[ShowReportOnWebPortal] [bit] NOT NULL CONSTRAINT [DF_AdditionalReporting_ShowReportOnWebPortal]  DEFAULT ((0)),
	[ManCoDocID] [int] NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[AppEnvironment]    Script Date: 21/04/2015 11:34:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[AppEnvironment](
	[AppEnvironmentID] [int] IDENTITY(1,1) NOT NULL,
	[Name] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_Environment] PRIMARY KEY CLUSTERED 
(
	[AppEnvironmentID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[aspnet_SchemaVersions]    Script Date: 21/04/2015 11:34:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[aspnet_SchemaVersions](
	[Feature] [nvarchar](128) NOT NULL,
	[CompatibleSchemaVersion] [nvarchar](128) NOT NULL,
	[IsCurrentVersion] [bit] NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[Feature] ASC,
	[CompatibleSchemaVersion] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[aspnet_Users]    Script Date: 21/04/2015 11:34:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[aspnet_Users](
	[ApplicationId] [uniqueidentifier] NOT NULL,
	[UserId] [uniqueidentifier] NOT NULL,
	[UserName] [nvarchar](256) NOT NULL,
	[LoweredUserName] [nvarchar](256) NOT NULL,
	[MobileAlias] [nvarchar](16) NULL,
	[IsAnonymous] [bit] NOT NULL,
	[LastActivityDate] [datetime] NOT NULL,
	[PreferredLandingPage] [nvarchar](250) NULL,
	[PreferredEnvironment] [nvarchar](250) NULL,
PRIMARY KEY NONCLUSTERED 
(
	[UserId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[aspnet_WebEvent_Events]    Script Date: 21/04/2015 11:34:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[aspnet_WebEvent_Events](
	[EventId] [char](32) NOT NULL,
	[EventTimeUtc] [datetime] NOT NULL,
	[EventTime] [datetime] NOT NULL,
	[EventType] [nvarchar](256) NOT NULL,
	[EventSequence] [decimal](19, 0) NOT NULL,
	[EventOccurrence] [decimal](19, 0) NOT NULL,
	[EventCode] [int] NOT NULL,
	[EventDetailCode] [int] NOT NULL,
	[Message] [nvarchar](1024) NULL,
	[ApplicationPath] [nvarchar](256) NULL,
	[ApplicationVirtualPath] [nvarchar](256) NULL,
	[MachineName] [nvarchar](256) NOT NULL,
	[RequestUrl] [nvarchar](1024) NULL,
	[ExceptionType] [nvarchar](256) NULL,
	[Details] [ntext] NULL,
PRIMARY KEY CLUSTERED 
(
	[EventId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[AspNetRoles]    Script Date: 21/04/2015 11:34:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[AspNetRoles](
	[Id] [nvarchar](128) NOT NULL,
	[Name] [nvarchar](max) NOT NULL,
 CONSTRAINT [PK_dbo.AspNetRoles] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
/****** Object:  Table [dbo].[AspNetUserClaims]    Script Date: 21/04/2015 11:34:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[AspNetUserClaims](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[ClaimType] [nvarchar](max) NULL,
	[ClaimValue] [nvarchar](max) NULL,
	[User_Id] [nvarchar](128) NOT NULL,
 CONSTRAINT [PK_dbo.AspNetUserClaims] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
/****** Object:  Table [dbo].[AspNetUserLogins]    Script Date: 21/04/2015 11:34:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[AspNetUserLogins](
	[UserId] [nvarchar](128) NOT NULL,
	[LoginProvider] [nvarchar](128) NOT NULL,
	[ProviderKey] [nvarchar](128) NOT NULL,
 CONSTRAINT [PK_dbo.AspNetUserLogins] PRIMARY KEY CLUSTERED 
(
	[UserId] ASC,
	[LoginProvider] ASC,
	[ProviderKey] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[AspNetUserRoles]    Script Date: 21/04/2015 11:34:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[AspNetUserRoles](
	[UserId] [nvarchar](128) NOT NULL,
	[RoleId] [nvarchar](128) NOT NULL,
 CONSTRAINT [PK_dbo.AspNetUserRoles] PRIMARY KEY CLUSTERED 
(
	[UserId] ASC,
	[RoleId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[AspNetUsers]    Script Date: 21/04/2015 11:34:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[AspNetUsers](
	[Id] [nvarchar](128) NOT NULL,
	[UserName] [nvarchar](256) NULL,
	[PasswordHash] [nvarchar](max) NULL,
	[SecurityStamp] [nvarchar](max) NULL,
	[FirstName] [nvarchar](256) NULL,
	[LastName] [nvarchar](256) NULL,
	[Title] [nvarchar](256) NULL,
	[Email] [nvarchar](256) NULL,
	[Comment] [nvarchar](max) NULL,
	[LastLoginDate] [datetime] NULL,
	[LastPasswordChangedDate] [datetime] NULL,
	[IsApproved] [bit] NULL,
	[IsLockedOut] [bit] NULL,
	[FailedLogInCount] [int] NULL,
	[Discriminator] [nvarchar](128) NOT NULL,
	[PreferredLandingPage] [nvarchar](250) NULL,
	[PreferredEnvironment] [nvarchar](250) NULL,
	[Phone] [nvarchar](256) NULL,
	[IsDeActivated] [bit] NOT NULL CONSTRAINT [DF_AspNetUsers_IsDeActivated]  DEFAULT ((0)),
 CONSTRAINT [PK_dbo.AspNetUsers] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
/****** Object:  Table [dbo].[AspNetUsers_New]    Script Date: 21/04/2015 11:34:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[AspNetUsers_New](
	[Id] [nvarchar](128) NOT NULL,
	[UserName] [nvarchar](256) NULL,
	[PasswordHash] [nvarchar](max) NOT NULL,
	[SecurityStamp] [nvarchar](max) NOT NULL,
	[FirstName] [nvarchar](256) NULL,
	[LastName] [nvarchar](256) NULL,
	[Title] [nvarchar](256) NULL,
	[Email] [nvarchar](256) NULL,
	[Comment] [nvarchar](max) NULL,
	[LastLoginDate] [smalldatetime] NULL,
	[LastPasswordChangedDate] [smalldatetime] NULL,
	[IsApproved] [bit] NULL,
	[IsLockedOut] [bit] NULL,
	[FailedLogInCount] [int] NULL,
	[Discriminator] [nvarchar](128) NOT NULL,
	[PreferredLandingPage] [nvarchar](250) NULL,
	[PreferredEnvironment] [nvarchar](250) NULL,
	[Phone] [nvarchar](256) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
/****** Object:  Table [dbo].[AuthoriseHistory]    Script Date: 21/04/2015 11:34:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[AuthoriseHistory](
	[ApprovalID] [int] IDENTITY(1,1) NOT NULL,
	[JobID] [int] NOT NULL,
	[ApprovalDate] [datetime] NOT NULL,
	[Comment] [nvarchar](500) NULL,
	[UserID] [nvarchar](128) NOT NULL,
 CONSTRAINT [PK_ApprovalHistory] PRIMARY KEY CLUSTERED 
(
	[ApprovalID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[BravuraUsernametoComitIDxref$]    Script Date: 21/04/2015 11:34:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[BravuraUsernametoComitIDxref$](
	[First Name] [nvarchar](255) NULL,
	[Last Name] [nvarchar](255) NULL,
	[UserID] [float] NULL,
	[Username] [nvarchar](255) NULL,
	[COMIT ID] [nvarchar](255) NULL,
	[Exact Match] [bit] NOT NULL,
	[email] [nvarchar](255) NULL,
	[F8] [nvarchar](255) NULL,
	[F9] [nvarchar](255) NULL,
	[F10] [nvarchar](255) NULL,
	[F11] [nvarchar](255) NULL
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[DeploymentHistory]    Script Date: 21/04/2015 11:34:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DeploymentHistory](
	[DeployHistID] [int] IDENTITY(1,1) NOT NULL,
	[ManCoDocID] [int] NOT NULL,
	[PromoteDate] [datetime] NOT NULL,
	[Label] [nvarchar](250) NULL,
	[UserID] [nvarchar](128) NULL,
	[Comment] [nvarchar](500) NULL,
 CONSTRAINT [PK_DeploymentHistory] PRIMARY KEY CLUSTERED 
(
	[DeployHistID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[DirectionType]    Script Date: 21/04/2015 11:34:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DirectionType](
	[DirectionTypeID] [int] IDENTITY(1,1) NOT NULL,
	[Direction] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_DirectionType] PRIMARY KEY CLUSTERED 
(
	[DirectionTypeID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[DocumentType]    Script Date: 21/04/2015 11:34:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DocumentType](
	[DocumentTypeID] [int] IDENTITY(1,1) NOT NULL,
	[BravuraDocTypeID] [int] NOT NULL,
	[BravuraDocTypeCode] [nvarchar](50) NOT NULL,
	[DocumentTypeName] [nvarchar](75) NOT NULL,
	[AdditionalSetup] [bit] NULL,
	[AgentDocument] [bit] NULL,
 CONSTRAINT [PK_DocumentType] PRIMARY KEY CLUSTERED 
(
	[DocumentTypeID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[DocumentVersion]    Script Date: 21/04/2015 11:34:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DocumentVersion](
	[DocVersionID] [int] IDENTITY(1,1) NOT NULL,
	[PubFileName] [nvarchar](500) NOT NULL,
	[EnvironmentID] [int] NOT NULL,
	[Version] [nvarchar](10) NULL,
	[Region] [nvarchar](50) NULL,
	[VersionComments] [nvarchar](250) NULL,
 CONSTRAINT [PK_PubFile] PRIMARY KEY CLUSTERED 
(
	[DocVersionID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[EnclosingJob]    Script Date: 21/04/2015 11:34:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[EnclosingJob](
	[EnclosingJobID] [int] IDENTITY(1,1) NOT NULL,
	[FBE_ID] [int] NOT NULL CONSTRAINT [DF_EnclosingJob_FBE_ID]  DEFAULT ((0)),
	[JobID] [int] NOT NULL,
	[MediaDefID] [int] NOT NULL,
	[Packs] [int] NULL,
	[Pages] [int] NULL,
	[Sheets] [int] NULL,
	[Filename] [nvarchar](250) NULL,
	[DigiQGRID] [nvarchar](30) NULL,
	[PostalDocketNumber] [nvarchar](30) NULL,
 CONSTRAINT [PK_EnclosingJob] PRIMARY KEY CLUSTERED 
(
	[EnclosingJobID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[EnvironmentType]    Script Date: 21/04/2015 11:34:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[EnvironmentType](
	[EnvironmentID] [int] IDENTITY(1,1) NOT NULL,
	[EnvironmentType] [nvarchar](30) NOT NULL,
	[ProcessingServerName] [nvarchar](150) NULL,
	[ADF_DB_Name] [nvarchar](150) NULL,
	[ADF_DB_ServerName] [nvarchar](150) NULL,
	[AppEnvironmentID] [int] NOT NULL,
	[BravuraDOCSEnvironmentType] [nvarchar](50) NULL,
	[BravuraDOCSEnvironmentChar] [char](1) NULL,
	[EnvironmentChar] [char](1) NULL,
 CONSTRAINT [PK_EnvironmentType] PRIMARY KEY CLUSTERED 
(
	[EnvironmentID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[FieldRoles]    Script Date: 21/04/2015 11:34:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[FieldRoles](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Description] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_FieldRoles] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[FileType]    Script Date: 21/04/2015 11:34:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[FileType](
	[FileTypeID] [int] IDENTITY(1,1) NOT NULL,
	[FileType] [nvarchar](75) NULL,
 CONSTRAINT [PK_FileType] PRIMARY KEY CLUSTERED 
(
	[FileTypeID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[Finishing]    Script Date: 21/04/2015 11:34:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Finishing](
	[FinishingDefID] [int] IDENTITY(1,1) NOT NULL,
	[FinishingDescription] [nvarchar](75) NOT NULL,
	[Unknown] [nchar](10) NULL,
 CONSTRAINT [PK_Finishing] PRIMARY KEY CLUSTERED 
(
	[FinishingDefID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[GlobalSettings]    Script Date: 21/04/2015 11:34:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[GlobalSettings](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[MinPasswordLength] [int] NOT NULL,
	[MinNonAlphaChars] [int] NOT NULL,
	[PasswordExpDays] [int] NOT NULL,
	[PassChangeBefore] [int] NOT NULL,
	[NewUserPasswordReset] [bit] NOT NULL,
	[MaxLogInAttempts] [int] NOT NULL DEFAULT ((0)),
 CONSTRAINT [PK_dbo.GlobalSettings] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[Group]    Script Date: 21/04/2015 11:34:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Group](
	[GroupID] [int] IDENTITY(1,1) NOT NULL,
	[GroupName] [nvarchar](50) NOT NULL,
	[GroupTypeID] [int] NOT NULL,
	[DocsGrpID] [int] NULL,
 CONSTRAINT [PK_Group] PRIMARY KEY CLUSTERED 
(
	[GroupID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[GroupDataRights]    Script Date: 21/04/2015 11:34:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[GroupDataRights](
	[GroupDR_ID] [int] IDENTITY(1,1) NOT NULL,
	[GroupID] [int] NOT NULL,
	[ManCoDocID] [int] NOT NULL,
	[EnvironmentID] [int] NOT NULL,
 CONSTRAINT [PK_tmpGroupDataRights] PRIMARY KEY CLUSTERED 
(
	[GroupDR_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[GroupDataRights_OLD_Corrupt]    Script Date: 21/04/2015 11:34:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[GroupDataRights_OLD_Corrupt](
	[GroupDR_ID] [int] IDENTITY(1,1) NOT NULL,
	[GroupID] [int] NOT NULL,
	[ManCoDocID] [int] NOT NULL,
	[EnvironmentID] [int] NOT NULL,
 CONSTRAINT [PK_GroupDataRights] PRIMARY KEY CLUSTERED 
(
	[GroupDR_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[GroupType]    Script Date: 21/04/2015 11:34:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[GroupType](
	[GroupTypeID] [int] IDENTITY(1,1) NOT NULL,
	[GroupTypeName] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_GroupType] PRIMARY KEY CLUSTERED 
(
	[GroupTypeID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[HPExstream_BundledIndexes]    Script Date: 21/04/2015 11:34:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[HPExstream_BundledIndexes](
	[BundleID] [int] IDENTITY(1,1) NOT NULL,
	[ManCoDocID] [int] NULL,
	[MediaDefID] [int] NULL,
	[StartIndexDefID] [int] NULL,
	[SplitIndexDefID] [int] NULL,
 CONSTRAINT [PK_HPExstream_BundledIndexes] PRIMARY KEY CLUSTERED 
(
	[BundleID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[HPExstreamControlFileSwitch]    Script Date: 21/04/2015 11:34:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[HPExstreamControlFileSwitch](
	[ControlSwitchID] [int] IDENTITY(1,1) NOT NULL,
	[ManCoDocID] [int] NOT NULL,
	[ControlFileSwitch] [varchar](100) NOT NULL,
 CONSTRAINT [PK_HPExstreamControlFileSwitch] PRIMARY KEY CLUSTERED 
(
	[ControlSwitchID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[HPExstreamReferenceFiles]    Script Date: 21/04/2015 11:34:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[HPExstreamReferenceFiles](
	[ReferenceFileID] [int] IDENTITY(1,1) NOT NULL,
	[ManCoDocID] [int] NOT NULL,
	[HPExstreamSymbolicName] [varchar](50) NOT NULL,
	[ReferenceFilePath] [varchar](500) NULL,
	[MediaReferenceFile] [bit] NOT NULL CONSTRAINT [DF_HPExstreamReferenceFiles_MediaReferenceFile]  DEFAULT ((0)),
 CONSTRAINT [PK_HPExstreamReferenceFiles] PRIMARY KEY CLUSTERED 
(
	[ReferenceFileID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[IndexDefinition]    Script Date: 21/04/2015 11:34:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[IndexDefinition](
	[IndexDefinitionID] [int] IDENTITY(1,1) NOT NULL,
	[ManCoDocID] [int] NOT NULL,
	[IndexDefinitionName] [nvarchar](150) NOT NULL,
	[ExstreamSymbolicIndexName] [nvarchar](150) NULL,
	[ExstreamSymbolicDataName] [nvarchar](150) NULL,
	[ExstreamSymbolicReportName] [nvarchar](150) NULL,
	[IndexDescription] [nvarchar](150) NULL,
	[ReportIsArchive] [bit] NOT NULL CONSTRAINT [DF_IndexDefinition_ReportIsArchive]  DEFAULT ((0)),
	[LayoutFileID] [int] NULL,
	[ReportLayoutFileID] [int] NULL,
	[MailSortSettingID] [int] NULL,
 CONSTRAINT [PK_IndexDefinition] PRIMARY KEY CLUSTERED 
(
	[IndexDefinitionID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[InputFile]    Script Date: 21/04/2015 11:34:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[InputFile](
	[FilenameID] [int] IDENTITY(1,1) NOT NULL,
	[Filename] [nvarchar](250) NULL,
	[FilePath] [nvarchar](250) NULL,
	[ReceivedDate] [datetime] NULL,
	[JobID] [int] NULL,
	[ManCoDocID] [int] NULL,
	[FileTypeID] [int] NULL,
 CONSTRAINT [PK_InputFile] PRIMARY KEY CLUSTERED 
(
	[FilenameID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[Job]    Script Date: 21/04/2015 11:34:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Job](
	[JobID] [int] IDENTITY(1,1) NOT NULL,
	[ManCoDocID] [int] NOT NULL,
	[GRID] [nvarchar](30) NOT NULL,
	[createDate] [datetime] NOT NULL,
	[FinishDate] [datetime] NULL,
	[SubmissionDate] [datetime] NULL,
	[AdditionalSetupInfo] [nvarchar](500) NULL,
	[UserID] [nvarchar](128) NULL,
	[ManuallySubmitted] [bit] NULL CONSTRAINT [DF_Job_ManuallySubmitted]  DEFAULT ((0)),
	[AllocatorGRID] [nvarchar](30) NULL,
	[JobStatusTypeID] [int] NULL CONSTRAINT [DF_Job_JobStatusTypeID]  DEFAULT ((30)),
	[Comment] [nvarchar](500) NULL,
 CONSTRAINT [PK_Job] PRIMARY KEY CLUSTERED 
(
	[JobID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[JobStatusType]    Script Date: 21/04/2015 11:34:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[JobStatusType](
	[JobStatusTypeID] [int] IDENTITY(1,1) NOT NULL,
	[JobStatusDescription] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_JobStausType] PRIMARY KEY CLUSTERED 
(
	[JobStatusTypeID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[LayoutArchiveFields]    Script Date: 21/04/2015 11:34:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[LayoutArchiveFields](
	[LayoutArchiveFieldID] [int] IDENTITY(1,1) NOT NULL,
	[LayoutFileID] [int] NULL,
	[LayoutFileType] [char](1) NULL,
	[ManCoDocID] [int] NULL,
	[FieldName] [varchar](50) NULL,
	[ArchiveIndexFieldName] [varchar](50) NULL,
	[FieldType] [varchar](50) NULL,
	[Offset] [int] NULL,
	[Length] [int] NULL,
	[FileNameOrder] [int] NULL,
	[TrimFieldSeparator] [bit] NOT NULL CONSTRAINT [DF_LayoutArchiveFields_TrimFieldSeparator]  DEFAULT ((0))
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[LayoutFile]    Script Date: 21/04/2015 11:34:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[LayoutFile](
	[LayoutFileID] [int] IDENTITY(1,1) NOT NULL,
	[BravuraRecFileID] [int] NOT NULL,
	[LayoutDescription] [varchar](150) NOT NULL,
	[LayoutFileType] [char](1) NOT NULL CONSTRAINT [DF_LayoutFiles_FileType]  DEFAULT ('R'),
	[MediaStageReportPacksField] [varchar](50) NULL,
	[MediaStageReportImagesField] [varchar](50) NULL,
	[MediaStageReportSheetsField] [varchar](50) NULL,
	[MediaStageReportConsolidatedPacksField] [varchar](50) NULL,
	[MediaStageReportAdditional1Field] [varchar](50) NULL,
	[MediaStageReportAdditional2Field] [varchar](50) NULL,
 CONSTRAINT [PK_IndexLayoutFile] PRIMARY KEY CLUSTERED 
(
	[LayoutFileID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[LayoutFileFields]    Script Date: 21/04/2015 11:34:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[LayoutFileFields](
	[LayoutFieldID] [int] IDENTITY(1,1) NOT NULL,
	[LayoutFileID] [int] NULL,
	[FieldName] [varchar](100) NULL,
	[FieldType] [varchar](50) NULL,
	[Offset] [int] NULL,
	[Length] [int] NULL,
	[FieldRole] [int] NULL,
 CONSTRAINT [PK_LayoutFileFields] PRIMARY KEY CLUSTERED 
(
	[LayoutFieldID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[MailsortSetting]    Script Date: 21/04/2015 11:34:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[MailsortSetting](
	[MailsortSettingID] [int] IDENTITY(1,1) NOT NULL,
	[TNTDepot] [nvarchar](50) NULL,
	[TNTMailingHouse] [nvarchar](50) NULL,
	[TNTClientCode] [nvarchar](50) NULL,
	[TNTJobReference] [nvarchar](50) NULL,
	[nexdoxMailsortService] [nvarchar](50) NULL,
	[largeletter] [bit] NULL,
	[FixedWeight_NoOuter] [int] NULL,
 CONSTRAINT [PK_MailsortSetting] PRIMARY KEY CLUSTERED 
(
	[MailsortSettingID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[ManagementCompany]    Script Date: 21/04/2015 11:34:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[ManagementCompany](
	[ManCoID] [int] IDENTITY(1,1) NOT NULL,
	[ManCoName] [nvarchar](75) NOT NULL,
	[ManCoCode] [nvarchar](50) NOT NULL,
	[ManCoShortName] [nvarchar](50) NOT NULL,
	[ParentCompanyID] [int] NOT NULL,
	[RufusDatabaseID] [varchar](50) NOT NULL,
	[Active] [bit] NULL,
	[BravuraManCoID] [int] NOT NULL CONSTRAINT [DF_ManagementCompany_BravuraManCoID]  DEFAULT ((0)),
	[StatementFileExtension] [varchar](10) NULL,
 CONSTRAINT [PK_ManagementCompany] PRIMARY KEY CLUSTERED 
(
	[ManCoID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[ManCoDocs]    Script Date: 21/04/2015 11:34:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ManCoDocs](
	[ManCoDocID] [int] IDENTITY(1,1) NOT NULL,
	[BravuraDocID] [int] NULL,
	[ManCoID] [int] NOT NULL,
	[DocumentTypeID] [int] NOT NULL,
	[PubFileName] [nvarchar](500) NOT NULL,
	[EnvironmentID] [int] NOT NULL,
	[Version] [nvarchar](10) NULL,
	[VersionComments] [nvarchar](250) NULL,
	[Active] [bit] NOT NULL CONSTRAINT [DF_ManCoDocs_Active]  DEFAULT ((1)),
	[Authorise] [bit] NULL,
	[ManualSubmissionOnly] [bit] NULL,
	[DateFolderCreation] [bit] NULL CONSTRAINT [DF_ManCoDocs_DateFolderCreation]  DEFAULT ((0)),
	[ManCoDocURI] [uniqueidentifier] NULL CONSTRAINT [DF_ManCoDocs_ManCoDocURI]  DEFAULT (newid()),
	[DateTimeStampInputFiles] [bit] NULL CONSTRAINT [DF_ManCoDocs_DateTimeStamp]  DEFAULT ((0)),
 CONSTRAINT [PK_ManCoDocs] PRIMARY KEY CLUSTERED 
(
	[ManCoDocID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[MaterialConsumption]    Script Date: 21/04/2015 11:34:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[MaterialConsumption](
	[MaterialConsumptionID] [int] IDENTITY(1,1) NOT NULL,
	[EnclosingID] [int] NOT NULL,
	[MediaStockId] [int] NOT NULL,
	[Qty] [int] NULL,
 CONSTRAINT [PK_MaterialConsumption] PRIMARY KEY CLUSTERED 
(
	[MaterialConsumptionID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[MediaDefinition]    Script Date: 21/04/2015 11:34:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[MediaDefinition](
	[MediaDefID] [int] IDENTITY(1,1) NOT NULL,
	[QueueName] [nvarchar](75) NOT NULL,
	[QueueNameSymbollic] [nvarchar](75) NULL,
	[ManCoDocID] [int] NOT NULL,
	[IndexDefinitionID] [int] NULL,
	[QueueDescription] [nvarchar](100) NULL,
	[Hold] [bit] NOT NULL,
	[BatchSizeSheets] [int] NULL,
	[MailsortSettingID] [int] NOT NULL,
	[FinishingDefID] [int] NOT NULL,
	[SortID] [int] NULL,
	[Print] [bit] NOT NULL CONSTRAINT [DF_MediaDefinition_NoPrint]  DEFAULT ((0)),
	[PDF_Only] [bit] NOT NULL,
	[createDate] [datetime] NULL,
	[createUserID] [nvarchar](128) NULL,
	[OutputFilename] [nvarchar](250) NULL,
	[OldQueueName] [nvarchar](75) NULL,
	[ReportHPExstreamSymbolicName] [varchar](50) NULL,
	[ReportLayoutFileID] [int] NULL,
	[ReportIsArchive] [bit] NOT NULL CONSTRAINT [DF_MediaDefinition_ReportIsArchive]  DEFAULT ((1)),
	[ReturnReportInDJA] [bit] NOT NULL CONSTRAINT [DF_MediaDefinition_ReturnReport]  DEFAULT ((0)),
	[ReturnOutput] [bit] NOT NULL CONSTRAINT [DF_MediaDefinition_ReturnOutput]  DEFAULT ((0)),
	[ShowOnShortReport] [bit] NOT NULL CONSTRAINT [DF_MediaDefinition_ShowOnShortReport]  DEFAULT ((1)),
	[ShowPDFOnWebPortal] [bit] NOT NULL CONSTRAINT [DF_MediaDefinition_ShowPDFOnWebPortal]  DEFAULT ((0)),
	[ShowReportOnWebPortal] [bit] NOT NULL CONSTRAINT [DF_MediaDefinition_ShowReportOnWebPortal]  DEFAULT ((0)),
	[IsCD] [bit] NOT NULL CONSTRAINT [DF_MediaDefinition_IsCD]  DEFAULT ((0)),
	[Colour] [bit] NOT NULL CONSTRAINT [DF_MediaDefinition_Colour]  DEFAULT ((0)),
	[EncloseEnvelope] [bit] NOT NULL CONSTRAINT [DF_MediaDefinition_EncloseEnvelope]  DEFAULT ((0)),
	[Action] [nvarchar](200) NULL,
 CONSTRAINT [PK_MediaDefinition] PRIMARY KEY CLUSTERED 
(
	[MediaDefID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[MediaStock]    Script Date: 21/04/2015 11:34:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[MediaStock](
	[MediaStockID] [int] IDENTITY(1,1) NOT NULL,
	[StockID] [int] NOT NULL,
	[MediaDefID] [int] NOT NULL,
	[PublishDate] [datetime] NOT NULL,
	[StationNumber] [int] NOT NULL,
 CONSTRAINT [PK_MediaStock] PRIMARY KEY CLUSTERED 
(
	[MediaStockID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[OM_UserGrps]    Script Date: 21/04/2015 11:34:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[OM_UserGrps](
	[DocID] [int] NOT NULL,
	[GroupID] [int] NOT NULL,
	[RunTypeID] [char](1) NOT NULL,
	[CompanyID] [int] NOT NULL,
	[ManCoCode] [char](2) NOT NULL,
	[DocCode] [char](3) NOT NULL,
	[ShortName] [varchar](30) NOT NULL,
	[Username] [nvarchar](50) NULL,
	[UserID] [int] NOT NULL,
	[Name] [nvarchar](40) NOT NULL,
	[ModeID] [char](1) NOT NULL,
	[COMIT ID] [nvarchar](255) NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[OM09072014DEV_UserGrps]    Script Date: 21/04/2015 11:34:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[OM09072014DEV_UserGrps](
	[DocID] [int] NOT NULL,
	[GroupID] [int] NOT NULL,
	[RunTypeID] [char](1) NOT NULL,
	[CompanyID] [int] NOT NULL,
	[ManCoCode] [char](2) NOT NULL,
	[DocCode] [char](3) NOT NULL,
	[ShortName] [varchar](30) NOT NULL,
	[Username] [nvarchar](50) NULL,
	[UserID] [int] NOT NULL,
	[Name] [nvarchar](40) NOT NULL,
	[ModeID] [char](1) NOT NULL,
	[COMIT ID] [nvarchar](255) NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[PackStores]    Script Date: 21/04/2015 11:34:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[PackStores](
	[PackID] [uniqueidentifier] NOT NULL,
	[Name1] [nvarchar](max) NULL,
	[Name2] [nvarchar](max) NULL,
	[Address1] [nvarchar](max) NULL,
	[Address2] [nvarchar](max) NULL,
	[Address3] [nvarchar](max) NULL,
	[Address4] [nvarchar](max) NULL,
	[Address5] [nvarchar](max) NULL,
	[Address6] [nvarchar](max) NULL,
	[PostCode] [nvarchar](max) NULL,
	[RecepientRef] [int] NOT NULL,
	[OwnerRef] [int] NULL,
	[Remake] [bit] NULL,
	[JobId] [int] NOT NULL,
	[InputFile] [nvarchar](max) NULL,
	[PackSeqNo] [int] NOT NULL,
	[EnclosingJobID] [int] NULL,
	[Pages] [int] NOT NULL,
	[CreationDate] [datetime] NOT NULL,
	[Pulled_UserId] [nvarchar](max) NULL,
	[Pulled] [bit] NOT NULL,
	[FBE_ID] [int] NULL,
 CONSTRAINT [PK_dbo.PackStores] PRIMARY KEY CLUSTERED 
(
	[PackID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
/****** Object:  Table [dbo].[ParentCompany]    Script Date: 21/04/2015 11:34:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ParentCompany](
	[ParentCompanyID] [int] IDENTITY(1,1) NOT NULL,
	[ParentCompany_Name] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_ParentCompany] PRIMARY KEY CLUSTERED 
(
	[ParentCompanyID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[PasswordHistories]    Script Date: 21/04/2015 11:34:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[PasswordHistories](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[PasswordHash] [nvarchar](max) NULL,
	[UserId] [nvarchar](128) NULL,
	[LogDate] [datetime] NOT NULL,
 CONSTRAINT [PK_dbo.PasswordHistories] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
/****** Object:  Table [dbo].[RufusDBCodes]    Script Date: 21/04/2015 11:34:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[RufusDBCodes](
	[RufusDBCodeID] [int] IDENTITY(1,1) NOT NULL,
	[ManCoID] [int] NULL,
	[RufusDBCode] [varchar](5) NOT NULL,
	[EnvironmentID] [int] NOT NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[SchemaVersions]    Script Date: 21/04/2015 11:34:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SchemaVersions](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[ScriptName] [nvarchar](255) NOT NULL,
	[Applied] [datetime] NOT NULL,
 CONSTRAINT [PK_SchemaVersions_Id] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[Sort]    Script Date: 21/04/2015 11:34:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Sort](
	[SortID] [int] IDENTITY(1,1) NOT NULL,
	[SortField] [nvarchar](75) NULL,
	[DirectionTypeID] [int] NOT NULL,
	[SortOrder] [int] NOT NULL CONSTRAINT [DF_Sort_SortOrder]  DEFAULT ((1)),
	[IndexDefinitionID] [int] NOT NULL,
 CONSTRAINT [PK_Sort] PRIMARY KEY CLUSTERED 
(
	[SortID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[Stock]    Script Date: 21/04/2015 11:34:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Stock](
	[StockID] [int] NOT NULL,
	[Code] [varchar](50) NOT NULL,
	[Description] [varchar](255) NOT NULL,
	[Weight] [int] NULL,
	[SizeX] [int] NULL,
	[SizeY] [int] NULL,
	[Facing] [char](1) NULL,
	[Division] [int] NULL,
	[ReorderLevel] [int] NULL,
	[StockTypeID] [int] NOT NULL,
	[User1] [varchar](50) NULL,
	[User2] [varchar](50) NULL,
	[User3] [varchar](50) NULL,
	[Overs] [float] NULL,
	[MakeReady] [int] NULL,
	[DateCreated] [datetime] NULL,
	[ChangedBy] [varchar](150) NULL,
	[Colour] [varchar](50) NOT NULL CONSTRAINT [DF_Stock_Colour]  DEFAULT ('White'),
 CONSTRAINT [PK_StockTable] PRIMARY KEY CLUSTERED 
(
	[StockID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[StockType]    Script Date: 21/04/2015 11:34:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[StockType](
	[StockTypeID] [int] IDENTITY(1,1) NOT NULL,
	[StockType] [varchar](50) NOT NULL,
 CONSTRAINT [PK_StockType] PRIMARY KEY CLUSTERED 
(
	[StockTypeID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[tmpCD_Data]    Script Date: 21/04/2015 11:34:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tmpCD_Data](
	[agentcode] [varchar](10) NULL,
	[InvestorCode] [varchar](10) NULL,
	[iName] [varchar](50) NULL,
	[fundType] [varchar](150) NULL,
	[fundName] [nvarchar](250) NULL,
	[shareValue] [nvarchar](30) NULL,
	[noOfshares] [nvarchar](30) NULL,
	[sequenceid] [varchar](10) NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[tmpUAT_Jobs]    Script Date: 21/04/2015 11:34:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tmpUAT_Jobs](
	[JobID] [int] IDENTITY(1,1) NOT NULL,
	[ManCoDocID] [int] NOT NULL,
	[GRID] [nvarchar](30) NOT NULL,
	[createDate] [datetime] NOT NULL,
	[FinishDate] [datetime] NULL,
	[SubmissionDate] [datetime] NULL,
	[AdditionalSetupInfo] [nvarchar](500) NULL,
	[UserID] [nvarchar](128) NULL,
	[ManuallySubmitted] [bit] NULL,
	[AllocatorGRID] [nvarchar](30) NULL,
	[JobStatusTypeID] [int] NULL,
	[Comment] [nvarchar](500) NULL
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[UserGroups]    Script Date: 21/04/2015 11:34:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[UserGroups](
	[UserGrp_ID] [int] IDENTITY(1,1) NOT NULL,
	[GroupID] [int] NOT NULL,
	[UserId] [nvarchar](128) NULL,
 CONSTRAINT [PK_UserGroups] PRIMARY KEY CLUSTERED 
(
	[UserGrp_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  View [dbo].[JobAuthoriseView]    Script Date: 21/04/2015 11:34:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE view [dbo].[JobAuthoriseView]

as

select ManCoDocId,

'JobAuthorise' = case 
    when Authorise = 1 then 'Manual'
	when Authorise = 0 and ManualSubmissionOnly = 0 then 'Auto'
	when Authorise = 0 and ManualSubmissionOnly = 1 then ''
	End, 
	Authorise ,ManualSubmissionOnly

from ManCoDocs


GO
/****** Object:  View [dbo].[LG_MONTHLY_JOBS]    Script Date: 21/04/2015 11:34:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[LG_MONTHLY_JOBS]
AS
SELECT        dbo.Job.JobID, dbo.Job.GRID, dbo.ManagementCompany.ManCoCode, dbo.DocumentType.BravuraDocTypeCode
FROM            dbo.Job INNER JOIN
                         dbo.ManCoDocs ON dbo.Job.ManCoDocID = dbo.ManCoDocs.ManCoDocID INNER JOIN
                         dbo.ManagementCompany ON dbo.ManCoDocs.ManCoID = dbo.ManagementCompany.ManCoID INNER JOIN
                         dbo.DocumentType ON dbo.ManCoDocs.DocumentTypeID = dbo.DocumentType.DocumentTypeID
WHERE        (dbo.ManagementCompany.ManCoCode = N'LG')

GO
/****** Object:  View [dbo].[UserAuthorisedView]    Script Date: 21/04/2015 11:34:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE view [dbo].[UserAuthorisedView]
as
select c.JobID, a.UserName,a.FirstName,a.LastName from 
AspNetUsers a inner join AuthoriseHistory b on a.Id = b.UserID
inner join Job c on b.JobID = c.JobID
GO
/****** Object:  View [dbo].[UserDocTypeView]    Script Date: 21/04/2015 11:34:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create view [dbo].[UserDocTypeView]
as
select distinct  a.UserId,c.DocumentTypeID
from 
UserGroups a inner join GroupDataRights b on a.GroupID = b.GroupID
inner join ManCoDocs c on b.ManCoDocID = c.ManCoDocID
GO
ALTER TABLE [dbo].[aspnet_Users] ADD  DEFAULT (newid()) FOR [UserId]
GO
ALTER TABLE [dbo].[aspnet_Users] ADD  DEFAULT (NULL) FOR [MobileAlias]
GO
ALTER TABLE [dbo].[aspnet_Users] ADD  DEFAULT ((0)) FOR [IsAnonymous]
GO
ALTER TABLE [dbo].[AdditionalReporting]  WITH NOCHECK ADD  CONSTRAINT [FK_AdditionalReporting_ManCoDocs] FOREIGN KEY([ManCoDocID])
REFERENCES [dbo].[ManCoDocs] ([ManCoDocID])
GO
ALTER TABLE [dbo].[AdditionalReporting] CHECK CONSTRAINT [FK_AdditionalReporting_ManCoDocs]
GO
ALTER TABLE [dbo].[AdditionalReporting]  WITH NOCHECK ADD  CONSTRAINT [FK_AdditionalReporting_MediaDefinition] FOREIGN KEY([MediaDefinitionID])
REFERENCES [dbo].[MediaDefinition] ([MediaDefID])
GO
ALTER TABLE [dbo].[AdditionalReporting] CHECK CONSTRAINT [FK_AdditionalReporting_MediaDefinition]
GO
ALTER TABLE [dbo].[AspNetUserClaims]  WITH NOCHECK ADD  CONSTRAINT [FK_dbo.AspNetUserClaims_dbo.AspNetUsers_User_Id] FOREIGN KEY([User_Id])
REFERENCES [dbo].[AspNetUsers] ([Id])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[AspNetUserClaims] CHECK CONSTRAINT [FK_dbo.AspNetUserClaims_dbo.AspNetUsers_User_Id]
GO
ALTER TABLE [dbo].[AspNetUserLogins]  WITH NOCHECK ADD  CONSTRAINT [FK_dbo.AspNetUserLogins_dbo.AspNetUsers_UserId] FOREIGN KEY([UserId])
REFERENCES [dbo].[AspNetUsers] ([Id])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[AspNetUserLogins] CHECK CONSTRAINT [FK_dbo.AspNetUserLogins_dbo.AspNetUsers_UserId]
GO
ALTER TABLE [dbo].[AspNetUserRoles]  WITH CHECK ADD  CONSTRAINT [FK_dbo.AspNetUserRoles_dbo.AspNetRoles_RoleId] FOREIGN KEY([RoleId])
REFERENCES [dbo].[AspNetRoles] ([Id])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[AspNetUserRoles] CHECK CONSTRAINT [FK_dbo.AspNetUserRoles_dbo.AspNetRoles_RoleId]
GO
ALTER TABLE [dbo].[AspNetUserRoles]  WITH CHECK ADD  CONSTRAINT [FK_dbo.AspNetUserRoles_dbo.AspNetUsers_UserId] FOREIGN KEY([UserId])
REFERENCES [dbo].[AspNetUsers] ([Id])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[AspNetUserRoles] CHECK CONSTRAINT [FK_dbo.AspNetUserRoles_dbo.AspNetUsers_UserId]
GO
ALTER TABLE [dbo].[AuthoriseHistory]  WITH NOCHECK ADD  CONSTRAINT [FK_ApprovalHistory_AspNetUsers] FOREIGN KEY([UserID])
REFERENCES [dbo].[AspNetUsers] ([Id])
GO
ALTER TABLE [dbo].[AuthoriseHistory] CHECK CONSTRAINT [FK_ApprovalHistory_AspNetUsers]
GO
ALTER TABLE [dbo].[AuthoriseHistory]  WITH NOCHECK ADD  CONSTRAINT [FK_ApprovalHistory_Job] FOREIGN KEY([JobID])
REFERENCES [dbo].[Job] ([JobID])
GO
ALTER TABLE [dbo].[AuthoriseHistory] CHECK CONSTRAINT [FK_ApprovalHistory_Job]
GO
ALTER TABLE [dbo].[DeploymentHistory]  WITH NOCHECK ADD  CONSTRAINT [FK_DeploymentHistory_ManCoDocs] FOREIGN KEY([ManCoDocID])
REFERENCES [dbo].[ManCoDocs] ([ManCoDocID])
GO
ALTER TABLE [dbo].[DeploymentHistory] CHECK CONSTRAINT [FK_DeploymentHistory_ManCoDocs]
GO
ALTER TABLE [dbo].[DocumentVersion]  WITH CHECK ADD  CONSTRAINT [FK_PubFile_EnvironmentType] FOREIGN KEY([EnvironmentID])
REFERENCES [dbo].[EnvironmentType] ([EnvironmentID])
GO
ALTER TABLE [dbo].[DocumentVersion] CHECK CONSTRAINT [FK_PubFile_EnvironmentType]
GO
ALTER TABLE [dbo].[EnclosingJob]  WITH NOCHECK ADD  CONSTRAINT [FK_EnclosingJob_Job] FOREIGN KEY([JobID])
REFERENCES [dbo].[Job] ([JobID])
GO
ALTER TABLE [dbo].[EnclosingJob] CHECK CONSTRAINT [FK_EnclosingJob_Job]
GO
ALTER TABLE [dbo].[EnclosingJob]  WITH NOCHECK ADD  CONSTRAINT [FK_EnclosingJob_MediaDefinition] FOREIGN KEY([MediaDefID])
REFERENCES [dbo].[MediaDefinition] ([MediaDefID])
GO
ALTER TABLE [dbo].[EnclosingJob] CHECK CONSTRAINT [FK_EnclosingJob_MediaDefinition]
GO
ALTER TABLE [dbo].[EnvironmentType]  WITH CHECK ADD  CONSTRAINT [FK_EnvironmentType_Environment] FOREIGN KEY([AppEnvironmentID])
REFERENCES [dbo].[AppEnvironment] ([AppEnvironmentID])
GO
ALTER TABLE [dbo].[EnvironmentType] CHECK CONSTRAINT [FK_EnvironmentType_Environment]
GO
ALTER TABLE [dbo].[Group]  WITH CHECK ADD  CONSTRAINT [FK_Group_GroupType] FOREIGN KEY([GroupTypeID])
REFERENCES [dbo].[GroupType] ([GroupTypeID])
GO
ALTER TABLE [dbo].[Group] CHECK CONSTRAINT [FK_Group_GroupType]
GO
ALTER TABLE [dbo].[GroupDataRights]  WITH CHECK ADD  CONSTRAINT [FK_GroupDataRights_Environment1] FOREIGN KEY([EnvironmentID])
REFERENCES [dbo].[AppEnvironment] ([AppEnvironmentID])
GO
ALTER TABLE [dbo].[GroupDataRights] CHECK CONSTRAINT [FK_GroupDataRights_Environment1]
GO
ALTER TABLE [dbo].[GroupDataRights]  WITH CHECK ADD  CONSTRAINT [FK_GroupDataRights_Group1] FOREIGN KEY([GroupID])
REFERENCES [dbo].[Group] ([GroupID])
GO
ALTER TABLE [dbo].[GroupDataRights] CHECK CONSTRAINT [FK_GroupDataRights_Group1]
GO
ALTER TABLE [dbo].[GroupDataRights]  WITH NOCHECK ADD  CONSTRAINT [FK_GroupDataRights_ManCoDocs1] FOREIGN KEY([ManCoDocID])
REFERENCES [dbo].[ManCoDocs] ([ManCoDocID])
GO
ALTER TABLE [dbo].[GroupDataRights] CHECK CONSTRAINT [FK_GroupDataRights_ManCoDocs1]
GO
ALTER TABLE [dbo].[GroupDataRights_OLD_Corrupt]  WITH CHECK ADD  CONSTRAINT [FK_GroupDataRights_Environment] FOREIGN KEY([EnvironmentID])
REFERENCES [dbo].[AppEnvironment] ([AppEnvironmentID])
GO
ALTER TABLE [dbo].[GroupDataRights_OLD_Corrupt] CHECK CONSTRAINT [FK_GroupDataRights_Environment]
GO
ALTER TABLE [dbo].[GroupDataRights_OLD_Corrupt]  WITH CHECK ADD  CONSTRAINT [FK_GroupDataRights_Group] FOREIGN KEY([GroupID])
REFERENCES [dbo].[Group] ([GroupID])
GO
ALTER TABLE [dbo].[GroupDataRights_OLD_Corrupt] CHECK CONSTRAINT [FK_GroupDataRights_Group]
GO
ALTER TABLE [dbo].[GroupDataRights_OLD_Corrupt]  WITH CHECK ADD  CONSTRAINT [FK_GroupDataRights_ManCoDocs] FOREIGN KEY([ManCoDocID])
REFERENCES [dbo].[ManCoDocs] ([ManCoDocID])
GO
ALTER TABLE [dbo].[GroupDataRights_OLD_Corrupt] CHECK CONSTRAINT [FK_GroupDataRights_ManCoDocs]
GO
ALTER TABLE [dbo].[HPExstreamControlFileSwitch]  WITH NOCHECK ADD  CONSTRAINT [FK_HPExstreamControlFileSwitch_ManCoDocs] FOREIGN KEY([ManCoDocID])
REFERENCES [dbo].[ManCoDocs] ([ManCoDocID])
GO
ALTER TABLE [dbo].[HPExstreamControlFileSwitch] CHECK CONSTRAINT [FK_HPExstreamControlFileSwitch_ManCoDocs]
GO
ALTER TABLE [dbo].[HPExstreamReferenceFiles]  WITH NOCHECK ADD  CONSTRAINT [FK_HPExstreamReferenceFiles_ManCoDocs] FOREIGN KEY([ManCoDocID])
REFERENCES [dbo].[ManCoDocs] ([ManCoDocID])
GO
ALTER TABLE [dbo].[HPExstreamReferenceFiles] CHECK CONSTRAINT [FK_HPExstreamReferenceFiles_ManCoDocs]
GO
ALTER TABLE [dbo].[IndexDefinition]  WITH NOCHECK ADD  CONSTRAINT [FK_IndexDefinition_MailsortSetting] FOREIGN KEY([MailSortSettingID])
REFERENCES [dbo].[MailsortSetting] ([MailsortSettingID])
GO
ALTER TABLE [dbo].[IndexDefinition] CHECK CONSTRAINT [FK_IndexDefinition_MailsortSetting]
GO
ALTER TABLE [dbo].[IndexDefinition]  WITH NOCHECK ADD  CONSTRAINT [FK_IndexDefinition_ManCoDocs] FOREIGN KEY([ManCoDocID])
REFERENCES [dbo].[ManCoDocs] ([ManCoDocID])
GO
ALTER TABLE [dbo].[IndexDefinition] CHECK CONSTRAINT [FK_IndexDefinition_ManCoDocs]
GO
ALTER TABLE [dbo].[InputFile]  WITH CHECK ADD  CONSTRAINT [FK_InputFile_FileType] FOREIGN KEY([FileTypeID])
REFERENCES [dbo].[FileType] ([FileTypeID])
GO
ALTER TABLE [dbo].[InputFile] CHECK CONSTRAINT [FK_InputFile_FileType]
GO
ALTER TABLE [dbo].[InputFile]  WITH CHECK ADD  CONSTRAINT [FK_InputFile_Job] FOREIGN KEY([JobID])
REFERENCES [dbo].[Job] ([JobID])
GO
ALTER TABLE [dbo].[InputFile] CHECK CONSTRAINT [FK_InputFile_Job]
GO
ALTER TABLE [dbo].[InputFile]  WITH NOCHECK ADD  CONSTRAINT [FK_InputFile_ManCoDocs] FOREIGN KEY([ManCoDocID])
REFERENCES [dbo].[ManCoDocs] ([ManCoDocID])
GO
ALTER TABLE [dbo].[InputFile] CHECK CONSTRAINT [FK_InputFile_ManCoDocs]
GO
ALTER TABLE [dbo].[Job]  WITH NOCHECK ADD  CONSTRAINT [FK_Job_ManCoDocs] FOREIGN KEY([ManCoDocID])
REFERENCES [dbo].[ManCoDocs] ([ManCoDocID])
GO
ALTER TABLE [dbo].[Job] CHECK CONSTRAINT [FK_Job_ManCoDocs]
GO
ALTER TABLE [dbo].[LayoutFileFields]  WITH CHECK ADD  CONSTRAINT [FK_LayoutFileFields_FieldRoles] FOREIGN KEY([FieldRole])
REFERENCES [dbo].[FieldRoles] ([ID])
GO
ALTER TABLE [dbo].[LayoutFileFields] CHECK CONSTRAINT [FK_LayoutFileFields_FieldRoles]
GO
ALTER TABLE [dbo].[ManagementCompany]  WITH NOCHECK ADD  CONSTRAINT [FK_ManagementCompany_ParentCompany] FOREIGN KEY([ParentCompanyID])
REFERENCES [dbo].[ParentCompany] ([ParentCompanyID])
GO
ALTER TABLE [dbo].[ManagementCompany] CHECK CONSTRAINT [FK_ManagementCompany_ParentCompany]
GO
ALTER TABLE [dbo].[ManCoDocs]  WITH NOCHECK ADD  CONSTRAINT [FK_ManCoDocs_DocumentType] FOREIGN KEY([DocumentTypeID])
REFERENCES [dbo].[DocumentType] ([DocumentTypeID])
GO
ALTER TABLE [dbo].[ManCoDocs] CHECK CONSTRAINT [FK_ManCoDocs_DocumentType]
GO
ALTER TABLE [dbo].[ManCoDocs]  WITH NOCHECK ADD  CONSTRAINT [FK_ManCoDocs_EnvironmentType] FOREIGN KEY([EnvironmentID])
REFERENCES [dbo].[EnvironmentType] ([EnvironmentID])
GO
ALTER TABLE [dbo].[ManCoDocs] CHECK CONSTRAINT [FK_ManCoDocs_EnvironmentType]
GO
ALTER TABLE [dbo].[ManCoDocs]  WITH NOCHECK ADD  CONSTRAINT [FK_ManCoDocs_ManagementCompany] FOREIGN KEY([ManCoID])
REFERENCES [dbo].[ManagementCompany] ([ManCoID])
GO
ALTER TABLE [dbo].[ManCoDocs] CHECK CONSTRAINT [FK_ManCoDocs_ManagementCompany]
GO
ALTER TABLE [dbo].[MaterialConsumption]  WITH CHECK ADD  CONSTRAINT [FK_MaterialConsumption_EnclosingJob] FOREIGN KEY([EnclosingID])
REFERENCES [dbo].[EnclosingJob] ([EnclosingJobID])
GO
ALTER TABLE [dbo].[MaterialConsumption] CHECK CONSTRAINT [FK_MaterialConsumption_EnclosingJob]
GO
ALTER TABLE [dbo].[MediaDefinition]  WITH NOCHECK ADD  CONSTRAINT [FK_MediaDefinition_AspNetUsers] FOREIGN KEY([createUserID])
REFERENCES [dbo].[AspNetUsers] ([Id])
GO
ALTER TABLE [dbo].[MediaDefinition] CHECK CONSTRAINT [FK_MediaDefinition_AspNetUsers]
GO
ALTER TABLE [dbo].[MediaDefinition]  WITH CHECK ADD  CONSTRAINT [FK_MediaDefinition_Finishing] FOREIGN KEY([FinishingDefID])
REFERENCES [dbo].[Finishing] ([FinishingDefID])
GO
ALTER TABLE [dbo].[MediaDefinition] CHECK CONSTRAINT [FK_MediaDefinition_Finishing]
GO
ALTER TABLE [dbo].[MediaDefinition]  WITH CHECK ADD  CONSTRAINT [FK_MediaDefinition_MailsortSetting] FOREIGN KEY([MailsortSettingID])
REFERENCES [dbo].[MailsortSetting] ([MailsortSettingID])
GO
ALTER TABLE [dbo].[MediaDefinition] CHECK CONSTRAINT [FK_MediaDefinition_MailsortSetting]
GO
ALTER TABLE [dbo].[MediaDefinition]  WITH CHECK ADD  CONSTRAINT [FK_MediaDefinition_ManCoDocs] FOREIGN KEY([ManCoDocID])
REFERENCES [dbo].[ManCoDocs] ([ManCoDocID])
GO
ALTER TABLE [dbo].[MediaDefinition] CHECK CONSTRAINT [FK_MediaDefinition_ManCoDocs]
GO
ALTER TABLE [dbo].[MediaStock]  WITH NOCHECK ADD  CONSTRAINT [FK_MediaStock_MediaDefinition] FOREIGN KEY([MediaDefID])
REFERENCES [dbo].[MediaDefinition] ([MediaDefID])
GO
ALTER TABLE [dbo].[MediaStock] CHECK CONSTRAINT [FK_MediaStock_MediaDefinition]
GO
ALTER TABLE [dbo].[PasswordHistories]  WITH CHECK ADD  CONSTRAINT [FK_dbo.PasswordHistories_dbo.AspNetUsers_UserId] FOREIGN KEY([UserId])
REFERENCES [dbo].[AspNetUsers] ([Id])
GO
ALTER TABLE [dbo].[PasswordHistories] CHECK CONSTRAINT [FK_dbo.PasswordHistories_dbo.AspNetUsers_UserId]
GO
ALTER TABLE [dbo].[Sort]  WITH NOCHECK ADD  CONSTRAINT [FK_Sort_DirectionType] FOREIGN KEY([DirectionTypeID])
REFERENCES [dbo].[DirectionType] ([DirectionTypeID])
GO
ALTER TABLE [dbo].[Sort] CHECK CONSTRAINT [FK_Sort_DirectionType]
GO
ALTER TABLE [dbo].[Sort]  WITH NOCHECK ADD  CONSTRAINT [FK_Sort_IndexDefinition] FOREIGN KEY([IndexDefinitionID])
REFERENCES [dbo].[IndexDefinition] ([IndexDefinitionID])
GO
ALTER TABLE [dbo].[Sort] CHECK CONSTRAINT [FK_Sort_IndexDefinition]
GO
ALTER TABLE [dbo].[UserGroups]  WITH CHECK ADD  CONSTRAINT [FK_UserGroups_AspNetUsers] FOREIGN KEY([UserId])
REFERENCES [dbo].[AspNetUsers] ([Id])
GO
ALTER TABLE [dbo].[UserGroups] CHECK CONSTRAINT [FK_UserGroups_AspNetUsers]
GO
ALTER TABLE [dbo].[UserGroups]  WITH CHECK ADD  CONSTRAINT [FK_UserGroups_Group] FOREIGN KEY([GroupID])
REFERENCES [dbo].[Group] ([GroupID])
GO
ALTER TABLE [dbo].[UserGroups] CHECK CONSTRAINT [FK_UserGroups_Group]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'A list of reports required for Index and Media Definitions.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'AdditionalReporting'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Additional control file switches required for RIL & some CNT documents. Legacy from Bravura DOCS' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'HPExstreamControlFileSwitch'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'This table should only ever be added to, records should NOT be changed.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'LayoutArchiveFields'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'This table should only ever be added to, records should NOT be changed.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'LayoutFile'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'This table should only ever be added to, records should NOT be changed.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'LayoutFileFields'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane1', @value=N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
Begin DesignProperties = 
   Begin PaneConfigurations = 
      Begin PaneConfiguration = 0
         NumPanes = 4
         Configuration = "(H (1[41] 4[20] 2[15] 3) )"
      End
      Begin PaneConfiguration = 1
         NumPanes = 3
         Configuration = "(H (1 [50] 4 [25] 3))"
      End
      Begin PaneConfiguration = 2
         NumPanes = 3
         Configuration = "(H (1 [50] 2 [25] 3))"
      End
      Begin PaneConfiguration = 3
         NumPanes = 3
         Configuration = "(H (4 [30] 2 [40] 3))"
      End
      Begin PaneConfiguration = 4
         NumPanes = 2
         Configuration = "(H (1 [56] 3))"
      End
      Begin PaneConfiguration = 5
         NumPanes = 2
         Configuration = "(H (2 [66] 3))"
      End
      Begin PaneConfiguration = 6
         NumPanes = 2
         Configuration = "(H (4 [50] 3))"
      End
      Begin PaneConfiguration = 7
         NumPanes = 1
         Configuration = "(V (3))"
      End
      Begin PaneConfiguration = 8
         NumPanes = 3
         Configuration = "(H (1[56] 4[18] 2) )"
      End
      Begin PaneConfiguration = 9
         NumPanes = 2
         Configuration = "(H (1 [75] 4))"
      End
      Begin PaneConfiguration = 10
         NumPanes = 2
         Configuration = "(H (1[66] 2) )"
      End
      Begin PaneConfiguration = 11
         NumPanes = 2
         Configuration = "(H (4 [60] 2))"
      End
      Begin PaneConfiguration = 12
         NumPanes = 1
         Configuration = "(H (1) )"
      End
      Begin PaneConfiguration = 13
         NumPanes = 1
         Configuration = "(V (4))"
      End
      Begin PaneConfiguration = 14
         NumPanes = 1
         Configuration = "(V (2))"
      End
      ActivePaneConfig = 0
   End
   Begin DiagramPane = 
      Begin Origin = 
         Top = 0
         Left = 0
      End
      Begin Tables = 
         Begin Table = "Job"
            Begin Extent = 
               Top = 6
               Left = 38
               Bottom = 135
               Right = 233
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "ManCoDocs"
            Begin Extent = 
               Top = 7
               Left = 248
               Bottom = 136
               Right = 463
            End
            DisplayFlags = 280
            TopColumn = 2
         End
         Begin Table = "ManagementCompany"
            Begin Extent = 
               Top = 7
               Left = 473
               Bottom = 136
               Right = 684
            End
            DisplayFlags = 280
            TopColumn = 0
         End
         Begin Table = "DocumentType"
            Begin Extent = 
               Top = 139
               Left = 250
               Bottom = 268
               Right = 454
            End
            DisplayFlags = 280
            TopColumn = 0
         End
      End
   End
   Begin SQLPane = 
   End
   Begin DataPane = 
      Begin ParameterDefaults = ""
      End
      Begin ColumnWidths = 9
         Width = 284
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
         Width = 1500
      End
   End
   Begin CriteriaPane = 
      Begin ColumnWidths = 11
         Column = 1440
         Alias = 900
         Table = 1170
         Output = 720
         Append = 1400
         NewValue = 1170
         SortType = 1350
         SortOrder = 1410
         GroupBy = 1350
         Filter = 1350
         Or = 1350
         Or = 1350
         Or = 1350
 ' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'LG_MONTHLY_JOBS'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane2', @value=N'     End
   End
End
' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'LG_MONTHLY_JOBS'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPaneCount', @value=2 , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'VIEW',@level1name=N'LG_MONTHLY_JOBS'
GO
